aspose file tools*
The moose likes JDBC and the fly likes Please state a simple and small example of a database schema which is 100% scalable Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Please state a simple and small example of a database schema which is 100% scalable" Watch "Please state a simple and small example of a database schema which is 100% scalable" New topic
Author

Please state a simple and small example of a database schema which is 100% scalable

Rajat Nigam
Greenhorn

Joined: Jul 03, 2013
Posts: 7
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.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61415
    
  67

You first. What's your answer?


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Rajat Nigam
Greenhorn

Joined: Jul 03, 2013
Posts: 7
If I could answer that then why would I question here.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61415
    
  67

So you cannot even being to start? What does a scalable schema mean to you?

(P.S. Being a wise-cracker isn't going to win you much help here. I'd advise a readjustment of attitude.)
Rajat Nigam
Greenhorn

Joined: Jul 03, 2013
Posts: 7
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.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42274
    
  64
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.


Ping & DNS - my free Android networking tools app
Rajat Nigam
Greenhorn

Joined: Jul 03, 2013
Posts: 7
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).
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42274
    
  64
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).
Rajat Nigam
Greenhorn

Joined: Jul 03, 2013
Posts: 7
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.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42274
    
  64
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 :-)
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1772
    
  14

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.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1772
    
  14

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,
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Please state a simple and small example of a database schema which is 100% scalable