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
 Please help with SQL table design
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlghost
Starting Member

23 Posts

Posted - 08/21/2006 :  01:06:41  Show Profile  Visit sqlghost's Homepage  Reply with Quote
Hi I'm having trouble figuring out this database design and normalising the data. Following are the tables and I am wonder if it can be normalised any further. This is a database of Architects and their work history and building they have designed.

Architects:
FirstName
LastName
Gender
DOB
Bio

Works:
Title
Client
Type_of_Building
Style
Address
Year_Designed
Year_Completed
Comments
Architect_ID

Firm: (this is the various firms that the architect has worked for)
CompanyName
Address

EmploymentHistory: (when the architect worked with a particular firm)
Year_Start
Year_End
Firm_ID
Architect_ID

Publication: (where the architects works were published)
Author
Date_Publication
Title
Source
Publisher
Architect_ID

I would really appreciate some help and suggestions on creating the relationships.
thanks

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 08/21/2006 :  05:54:33  Show Profile  Reply with Quote
It can definitely be normalised further. Do a google search on 3NF or Third Normal Form and read around the subject. This should give you a good idea of what to look out for. I would definitely suggest creating separate entities for: Gender, Client, BuildingType, Style, and Publisher at the very least. There are further issues with the Works entity. If multiple architects have worked on a particular project, you will have duplicate rows, with the potential for inconsistencies. Take architect out of here and create an additional bridging entity (called something like ArchitectWorks) to relate the two. The same issues could potentially crop up with Publication. Also, what's the difference here between Author and Architect (via ArchitectID)?

Mark
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 08/21/2006 :  06:25:49  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
First thing will be to add ID column column to each of the master tables like Architects, Firm, Publication, and normalize further as suggested by mwjdavidson

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 08/21/2006 :  11:36:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

http://www.datamodel.org/NormalizationRules.html


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sqlghost
Starting Member

23 Posts

Posted - 08/21/2006 :  21:35:26  Show Profile  Visit sqlghost's Homepage  Reply with Quote
Thanks for the replies.
I omitted the ID columns in each of the tables but I factored them in.

In the "Publication" table the Author refers to the Author of the Publication material. The ArchitectID would be the Architect about whom the Publication is.

I'll go through the links provided and hopefully make some more sense of my normalisation.
thanks.
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 08/21/2006 :  22:51:16  Show Profile  Reply with Quote
quote:

I would definitely suggest creating separate entities for: Gender, Client, BuildingType, Style, and Publisher at the very least.



Really? What NF are they breaking?

quote:

First thing will be to add ID column column to each of the master tables



It most definately is NOT the first thing to do.

First thing to do is to understand the data and its purpose. That is the hard part, the rest is surprisingly easy...

DavidM

Production is just another testing cycle
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.05 seconds. Powered By: Snitz Forums 2000