Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnc81
Starting Member
3 Posts |
 Posted - 2013-04-26 : 13:41:09
|
| Hi everybody,I am designing a database structure and I would like some advice regarding the tables. Say for example I am building a database which needs to record a building, the floors and the rooms on each floor. Would the best approach be to have three tables, one for each of the three criteria, or have one table. Taking the example I have given, I could have a structure like this:tblbuildingbuilding_idbuilding_nametblfloorbuilding_idfloor_idfloor_numbertblroomfloor_idroom_idroom_numberOr, I could have one table like this:tblbuildingbuilding_idbuilding_namefloor_numberroom_numberObviously the first approach means I am reducing the duplication of buidling names and floor numbers when compared to the second approach. I just wonder if the first approach is overkill? Is there a point at which normalization of data becomes too much?I welcome your advice and comments.Many Thanks,John |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-04-26 : 13:58:08
|
| I don't like putting it all in one table. That's why we describe entites in using predicate logic (is a versus has a), as in a Buiding has Floors. So I'm all for create separate entity tables. I'm not sure I like the idea of surrogating everything, but it seems pretty popular these days. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-29 : 00:37:58
|
| I prefer first approach. That will give you f;exibility to add as many floor and rooms under them as you want. also master entries (building,floor ) are maintained only once and so you dont need to do multiple updates for modifying any entries. This will reduce chances of update anomalies.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
SQL Table structure