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 |
|
jagd29
Starting Member
5 Posts |
 Posted - 2008-06-18 : 12:46:27
|
| The data that I have is the following:Car Manufacturer Factory CarCode---------------- ------- -------Ford Houston F-HouFord Reno R-HouChevy Houston C-HouChevy Las Vegas C-LVHonda SLC H-SLCFord SanFran F-SFChevy Miami C-MiaI have a database design implemented already, but I'm receiving some disagreement about it from a co-worker. The way I see it is you have a car manufacturer and a factory location that are both unique, so I created a lookup table for each. I then created a branch table for the many-to-many relationship that exists between Manufacturer and Factory, and within that branch table I placed a column for CarCode, because it seems to me that Manufacturer and Location both determine the CarCode.I also have an autonumber field within the branch table, which I then use for foreign key relationships.Is there anything wrong with my design here?Thank you in advance for your help! |
|
|
jagd29
Starting Member
5 Posts |
Posted - 2008-06-18 : 12:49:38
|
| Car Manufacturer * Factory * CarCode---------------- * ------- * -------Ford *********** Houston * F-HouFord *********** Reno ** * R-HouChevy ********** Houston * C-HouChevy ********** Las Vegas C-LVHonda ********** SLC ***** H-SLCFord *********** SanFran * F-SFChevy ********** Miami *** C-MiaSorry for the asteriks, but the white space is lost during post. :( |
 |
|
|
bjoerns
Posting Yak Master
154 Posts |
Posted - 2008-06-19 : 09:57:26
|
| Isn't the composite key (Manufacturer, Factory) the natural primary key of your table? |
|
|
|
bjoerns
Posting Yak Master
154 Posts |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2008-06-19 : 10:31:22
|
| It is really impossible to infer your domain rules from this small set of sample data. But this looks like a case for fifth normal form ([url]http://en.wikipedia.org/wiki/Fifth_normal_form[/url])I've never been good at 5NF, but wouldn't it go something like this?Table1{CarCode, CarManufacturer}Table2{CarManufacturer, Factory}Table3{CarCode, Factory}(...Name the tables whatever you like...I hate naming stuff)Jayto here knows when |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-06-24 : 06:24:23
|
| This is a perfectly normal way to solve a many-to-many relationship, I would just solve it slightly differently:DECLARE @CarManufacturer table (CarManufacturerID int, CarManufacturer)DECLARE @CarFactory table (CarFactoryID int, FactoryName)DECLARE @CarCode table (CarManufacturerID, CarFactoryID, CarCode)(and to keep your formatting add [ code ] and [ /code ] without the space around your code)- Lumbago |
|
|
|
|
|
|
|
|
How would you design this?