As far as DB is concerned I can't afford licenced version that could calibrate my application with features like sharding, partioning and auto-scalability.
While I'm not sure what you mean by "calibrate", this statement sits oddly with the one about the client being a large telco. Surely they have a very substantial commercial DB in place that this solution should run on?
What you say about downtime makes sense in that context. I think you need to get a much better sense of what nature the changes may be, and what the involved data quantities are, so that you can architect the solution with that in mind. (As an aside, I think you're overestimating how often "changing requirements" implies "changing the DB schema" with a company like this. If it does, they may need better IT governance.)
As an aside, none of this has anything to do with scalability: it is about making seemless schema changes. In fact, an approach like this makes it very hard, if not impossible, to tune the DB using indexes and query optimization. You need to make sure that it scales with the amount of data they have, and expect to have in two years time. It is, in most ways, a horrible approach, because it throws away most of what SQL and relational DB can do for a questionable gain.
An obvious approach would be a schema like the following. It provides for 4 data types (int, double, varchar and datetime); it's obvious how to extend it for more types. Or you could eschew the concept of types altogether and store everything as varchar/text. That would require much more storage space, and would be much slower due to type conversions, but with a company like that, storage space should not be a major concern, and performance is likely to be bad anyway with a solution like this. One could forego the
table_attributes table, so that each values table would have the table and attribute IDs directly.
tables: id int, name varchar
types: id int, name varchar, kind int (
kind would indicate one of int, double, varchar, datetime)
attributes: id int, name varchar, type_id int fkey
table_attributes: id int, table_id int fkey, attribute_id int fkey
int_values: id int, table_attribute_id int fkey, value int
double_values: id int, table_attribute_id int fkey, value double
varchar_values: id int, table_attribute_id int fkey, value varchar
datetime_values: id int, table_attribute_id int fkey, value datetime
It was a fun intellectual exercise to come up with this, but don't ever put anything like this into production, the DBAs are going to come after you :-)
1
