Please help with SQL table design

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help with SQL table design

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:
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

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 mwjdavidson

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-21 : 11:36:49

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

Madhivanan

Failing to plan is Planning to fail

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...

DavidM

Production is just another testing cycle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources