SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Table structure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnc81
Starting Member

Seychelles
3 Posts

Posted - 04/26/2013 :  13:41:09  Show Profile  Reply with Quote
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:

tblbuilding
building_id
building_name

tblfloor
building_id
floor_id
floor_number

tblroom
floor_id
room_id
room_number


Or, I could have one table like this:

tblbuilding
building_id
building_name
floor_number
room_number


Obviously 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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/26/2013 :  13:58:08  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/29/2013 :  00:37:58  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000