Please state a simple and small example of a database schema which is 100% scalable
Rajat Nigam
Greenhorn
Posts: 7
Rajat Nigam
Greenhorn
Posts: 7
Rajat Nigam
Greenhorn
Posts: 7
posted 3 years ago
Thanks for your attitude adjustment advice. Conside me as a vardant in scalibility.
Example: I have a database "EmployeeMaster" containing tables -> EmployeeLogin -> EmployeeDetails -> EmployeeAccounts
These attributes of these tables are subject to increase in future. Also, more tables can be added.
Now, the challenge is to design the database in such a way where I don't need to Alter the tables if every time I need to insert an attribute into them. Also, this attribute could be of any type like int, varchar, binary etc.
Example: I have a database "EmployeeMaster" containing tables -> EmployeeLogin -> EmployeeDetails -> EmployeeAccounts
These attributes of these tables are subject to increase in future. Also, more tables can be added.
Now, the challenge is to design the database in such a way where I don't need to Alter the tables if every time I need to insert an attribute into them. Also, this attribute could be of any type like int, varchar, binary etc.
Ulf Dittmer
Rancher
Posts: 42970
73
posted 3 years ago
While there is no principal difficulty in doing that in a relational DB, you lose the benefits of using SQL directly on those attributes and tables. A relational DB might not be the best kind of DB for such purposes. How often do you envision this kind of change to happen? Can you given some examples of such changes?
In production DBs the need for schema changes doesn't arise often, and when it does, the software generally needs to be adapted to accommodate those added fields and tables anyway - so a DB update tends not to be a big deal.
In production DBs the need for schema changes doesn't arise often, and when it does, the software generally needs to be adapted to accommodate those added fields and tables anyway - so a DB update tends not to be a big deal.
Rajat Nigam
Greenhorn
Posts: 7
posted 3 years ago
But I want to come up with an approach where I just design a schema in such a way that, in case if requirements come up in future then it can accommodate those requirements on its own.
Like my Db contains two tables
1. ProductDetails contains "productId" and other mandatory details
2. ProductVariant initially contains two attributes "color(varchar)" and "price(double)".
Now an application start operating on these two tables. Now in case if I need two add two more attributes like "weight(float)" and "image(binary)" in ProductVariant without altering this table or shutting down my application.
I hope you guys understand what I'm trying to convey.
Assist me to come up with a relevant solution(In brief).
Like my Db contains two tables
1. ProductDetails contains "productId" and other mandatory details
2. ProductVariant initially contains two attributes "color(varchar)" and "price(double)".
Now an application start operating on these two tables. Now in case if I need two add two more attributes like "weight(float)" and "image(binary)" in ProductVariant without altering this table or shutting down my application.
I hope you guys understand what I'm trying to convey.
Assist me to come up with a relevant solution(In brief).
Ulf Dittmer
Rancher
Posts: 42970
73
posted 3 years ago
Yep, I got that. I'm just curious why you want to do that, especially given that you will likely need to adapt the code that deals with that data anyway. Plus, it will likely have an impact on the underlying architecture.
Why would shutting down the app be a big deal? Most web sites are inaccessible during updates that change code and/or schema (and if they're not, they probably have a large team to support it :-) ).
And as I said, you should consider whether relational DBs are the best kind of DB for such a purpose (they're likely not, IMO).
Why would shutting down the app be a big deal? Most web sites are inaccessible during updates that change code and/or schema (and if they're not, they probably have a large team to support it :-) ).
And as I said, you should consider whether relational DBs are the best kind of DB for such a purpose (they're likely not, IMO).
Rajat Nigam
Greenhorn
Posts: 7
posted 3 years ago
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.
My application serves a telecom gaint in 24*7 operating enviornment. So shutting down application(which takes 13mins to work like before after re-deployment) for even 15 mins cuts down there connectivity in 9 cities. Which means their 2 million customers are untraceble for half an hour.
As the clients requirements changes on regular basis.
This whole procedure lands me bearing heavy loss.
Also, the size of support team is quite small that can handle the operations while I update the patch.
I hope you got the answer for why I'm looking for the one stop solution for my DB schema.
Now, could you please help me to figure out solution for my problem.
My application serves a telecom gaint in 24*7 operating enviornment. So shutting down application(which takes 13mins to work like before after re-deployment) for even 15 mins cuts down there connectivity in 9 cities. Which means their 2 million customers are untraceble for half an hour.
As the clients requirements changes on regular basis.
This whole procedure lands me bearing heavy loss.
Also, the size of support team is quite small that can handle the operations while I update the patch.
I hope you got the answer for why I'm looking for the one stop solution for my DB schema.
Now, could you please help me to figure out solution for my problem.
Ulf Dittmer
Rancher
Posts: 42970
73
posted 3 years ago
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
-
-
-
-
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 :-)
posted 3 years ago
There are no "100% scalable" databases - everything will grind to a halt sooner or later if you throw enough data at it.
However, you might want to look at one of the different NoSQL databases to give you some of what you are looking for. For example, MongoDB might give you the flexible schema - Mongo "collections" are like tables, but you can have different attributes in different records in the same collection because Mongo does not enforce the record ("document") structure.
Also, most of the NoSQL DBs - including MongoDB - are designed with scalability across multiple servers in mind e.g. via sharding/replication etc. However, most of them have to make certain compromises in order to achieve this e.g. by sacrificing things like consistency in transactions (ACID vs BASE transaction characteristics) or not supporting transactions at all.
But you can do your own homework investigating this - perhaps start with Seven Databases In Seven Weeks if you want a quick overview of some of the main NoSQL DBs and their distinctive features.
Rajat Nigam wrote:I need a simple and short example of a database schema which is 100% scalable. Take any case of your choice. It could be a Student or Employee Database. Or any sample but simple target scenario.
Please provide necessary explanation along with it.
Thanks.
There are no "100% scalable" databases - everything will grind to a halt sooner or later if you throw enough data at it.
However, you might want to look at one of the different NoSQL databases to give you some of what you are looking for. For example, MongoDB might give you the flexible schema - Mongo "collections" are like tables, but you can have different attributes in different records in the same collection because Mongo does not enforce the record ("document") structure.
Also, most of the NoSQL DBs - including MongoDB - are designed with scalability across multiple servers in mind e.g. via sharding/replication etc. However, most of them have to make certain compromises in order to achieve this e.g. by sacrificing things like consistency in transactions (ACID vs BASE transaction characteristics) or not supporting transactions at all.
But you can do your own homework investigating this - perhaps start with Seven Databases In Seven Weeks if you want a quick overview of some of the main NoSQL DBs and their distinctive features.
No more Blub for me, thank you, Vicar.
posted 3 years ago
Well, if your telecom giant is so critically dependent on this database that it cannot afford any downtime, then even if you are using an open source DB you might still need to invest a little money in appropriate tools, licences or support options to ensure this level of service,
Rajat Nigam wrote: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. My application serves a telecom gaint in 24*7 operating enviornment.
Well, if your telecom giant is so critically dependent on this database that it cannot afford any downtime, then even if you are using an open source DB you might still need to invest a little money in appropriate tools, licences or support options to ensure this level of service,
No more Blub for me, thank you, Vicar.
| It is sorta covered in the JavaRanch Style Guide. |