| Author |
 Topic  |
|
|
sqlghost
Starting Member
23 Posts |
 Posted - 08/21/2006 : 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:
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
|
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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 08/21/2006 : 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 mwjdavidson
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
|
|
sqlghost
Starting Member
23 Posts |
Posted - 08/21/2006 : 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
Australia
1591 Posts |
Posted - 08/21/2006 : 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...
DavidM
Production is just another testing cycle |
|
|
| |
Topic |
|
Please help with SQL table design
