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 |
sqlghost
Starting Member
23 Posts |
Posted - 2006-08-21 : 01:06:41
|
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:FirstNameLastNameGenderDOBBioWorks:TitleClientType_of_BuildingStyleAddressYear_DesignedYear_CompletedCommentsArchitect_IDFirm: (this is the various firms that the architect has worked for)CompanyNameAddressEmploymentHistory: (when the architect worked with a particular firm)Year_StartYear_EndFirm_IDArchitect_IDPublication: (where the architects works were published)AuthorDate_PublicationTitleSourcePublisherArchitect_IDI would really appreciate some help and suggestions on creating the relationships.thanks |
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-08-21 : 05:54:33
|
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 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-08-21 : 06:25:49
|
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 mwjdavidsonHarsh AthalyeIndia."Nothing is Impossible" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
sqlghost
Starting Member
23 Posts |
Posted - 2006-08-21 : 21:35:26
|
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. |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-08-21 : 22:51:16
|
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...DavidMProduction is just another testing cycle |
|
|
|
|
|
|
|