Designe issue

Question

Designe issue

sreenath

SSC Enthusiast

Points: 184
More actions
October 3, 2005 at 11:46 pm

#102836

Hi
I dont know whether this is the right place to ask this question but as this forum is marked "GENERAL" i am going forward with my question.
This question resemble's the actual problem i have..
Consider a state X who has 10 districts and each of this district have 100 schools,how should i store information of this schools? my current approach is
for each district i have a database created,becoz i know the no of school is going to grow exponentialy ie tomarow there will be another 1000 schools and they after another 1 lakh and it will go on.
so District1(Database)>School (Table)
District2(Database)>School(Table) or
District(Database) and all school information in one table

Please provide a guidlines ...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

This is fairly straightforward. You do not need separate databases for this information, even if the number of schools gets very large - you will find that having everything in the same db will be easier to maintain in the long run, in my opinion.

Here's an example of a suggested table structure:

District(DistrictID, DistrictName, StateID)

(DistrictID is the primary Key, StateID is a foreign key to the State table:

State(StateID, StateName)

(StateID is the PK)

and finally

School(SchoolID, DistrictID)

(SchoolID is the PK, DistrictID is FK to the District table)

I suggest that you do not think of a district as 'having schools' and rather think of a school as having district as an attribute.

Regards

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sreenath SSC Enthusiast Points: 184 More actions · Answer 2

Hi Phil Parkin

Thanks for the reply, but the problem at hand is IF i put all school information in single table say for eg

i have 10 district each have say 1 million school's(i have taken this school district as just an example real combination is something else) so total records in the single table will be (10*One million) ok fine let me accept table can handle it.BUT we are sure that district and school will grow exponetially ie

one month down the line it will reach 100 ditstricts and 2 million or more in each district so what will be the situation of the table, i am worried becoz we are sure that 2 to 3 years down the line we will have trillions and trillions of records NOW what to do??

Thanks

Sree

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 3

you could always look at horizontally partitioning the table if the size becomes too much to handle.

David Burrows SSC Guru Points: 65142 More actions · Answer 4

have 10 district each have say 1 million school's(i have taken this school district as just an example real combination is something else) so total records in the single table will be (10*One million) ok fine let me accept table can handle it.BUT we are sure that district and school will grow exponetially ie

Your supposition is incorrect. Each state will occur once, each district will occur once (a district can only be in one state) and each school will occur only once (a school can only be in once district). Based on your original post, there would be one row in the state table, 10 rows in the district table (linked to state table) and 100 rows in the school table (linked to district). If you add a school then at most you would add 1 row to each table (if the district and/or state not present)

Since this is hypothetical, and therefore assuming your data does not resemble the above, you will need to denormalize the data based on the relationships (one-to-one, one-to-many) between State,District,School (or whatever they are called).

Far away is close at hand in the images of elsewhere.
Anon.