October 28, 2010 at 7:37 am
while I've been coding for years (remember when DreamWeaver kicked *** and web developers wrote javascript ???), I'm no sql guru.
I have seen this design a lot lately and it seems like overkill to me.. and anecdotally this seems to be related to the rise in ORM tools like Entity Framework. Or maybe I'm just seeing things.
Why would this
dbo.Man {manid, ......}
dbo.ManCar {id , fk_manid, fk_carid}
dbo.Car {carid,...}
be better than this
dbo.Man {manid, ......}
dbo.Car {carid,fk_manid...}
when we know that a MAN may own one or more CARS and not visa-versa?
October 28, 2010 at 7:42 am
If we know that a car can have only one owner and it can’t have more then one owner, then I’d go with a foreign key like you suggested. The question is – are you sure that a car can’t belong to more then one person?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 28, 2010 at 8:00 am
What if it's a rental or a "shared" car arrangement (e.g. Zipcar)?
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
October 28, 2010 at 8:02 am
The database design has to be able to handle all cases. Even if 99.99% of cars have only one owner, you need to allow for the 0.01% where there are multiple owners.
When you are modeling the data and you are not sure if it is one to many, or many to many, then a many to many design is a safer way to handle the possibility.
October 28, 2010 at 8:03 am
In that particular instance, I'd say more than one person can "own" a car/house/whatever. Divorce proceedings often seem to support that concept.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 8:52 am
OK let me try that again:-D. Damn good thing I didn't go with my Teacher ==> Student example.
I understand the 1% argument and maybe that is the lesson here and the answer to my question.
In my current example we have multiple site addresses for each client and, by design, it should be a one-to-many relationship (a client can have multiple with different css branding)
dbo.client
dbo.client_site
dbo.site
...but the vendor who wrote the original version may(read probably) not have had full specs or instructions.
So maybe this is a philosophical argument/question?
Should we really code for the 1% as opposed to coding for effeciency and/or clarity. Just a thought.
October 28, 2010 at 9:32 am
You need to design the database to model the reality of the data as closely as possible. If there is a many to many relationship, even in a small percentage of the data, then you need to design the database to handle that.
if you don’t, then you will end up with very ugly hacks to work around the design error, and you will have a very inefficient system.
In your second example, maybe the designers were told that it was possible for sites to have multiple clients in the future, or maybe they were unsure if it could ever happen.
October 28, 2010 at 2:08 pm
I can easily see two companies co-branding into a single website. I'm not sure if that's applicable, but it could happen.
Also, you could easily end up with a parent company being a co-owner of a child company. Again, not sure that's applicable to your situation, but it's a business possibility.
Really, I'd check and see if there are any legitimate multi-owner databases already in the system. If not, you're probably safe to make it 1:* instead of *:*.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2010 at 6:03 am
Forget Man & Car... what about when you have Customers & Products
Customer (custtid, ...)
Orders (orderid, custid, prodid, ...)
Products (prod id, ...)
Orders sits b/w Customer & Products as one customer can have multiple products and vice versa, one product can be taken by multiple Customers.
November 10, 2010 at 6:38 am
I don't know if this fits your business application, but what if a bank holds the loan on a car? Do you list 2 owners for the car, the bank and the person who drives it every day?
M:M joining tables can be a pain; not having them when you need them is a bigger pain! 😉
Rich
November 10, 2010 at 7:59 am
I'm not sure something important isn't being overlooked. Consider the original table:
dbo.Man {manid, ......}dbo.ManCar {id , fk_manid, fk_carid}dbo.Car {carid,...}
Note that the man/car has a primary key and two fks that when combined could serve as the primary key. In this case, id might be superfluous.
Consider another situation. Technicians are required to have certain requirements to work in a state. A technician may have the approval from multiple states, and of course a state will have many technicians approved. Should the model be:
dbo.TechState {PK[id], FK_techid, FK_stateid}
-or-
dbo.TechState {PK[FK_techid, FK_stateid]}
In this case, I think the second works better because I don't want to repeat an entry for the same tech in the same state.
Consider the video store. Which works better:
dbo.Rental {PK[id], FK_itemid, FK_custid}
-or-
dbo.Rental {PK[FK_itemid, FK_custid]}
In this case, I think the first works better. I don't care if a particular customer rents and items again and again, so I need a system generated id. (yes, I could use date, but that's not a solid solution).
So back to to the original post, I would have issues with both of the suggestions, unless it were a rental agency, as the same customer may rent the same car. If it's not, I don't see multiple owners as a 1% solution. I would expect that most married couples are joint owners of their cars.
November 12, 2010 at 1:37 pm
RonKyle (11/10/2010)
Consider the video store. Which works better:dbo.Rental {PK[id], FK_itemid, FK_custid}
-or-
dbo.Rental {PK[FK_itemid, FK_custid]}
In this case, I think the first works better. I don't care if a particular customer rents and items again and again, so I need a system generated id. (yes, I could use date, but that's not a solid solution).
The second example is better. You really should care if any table has duplicate rows. Duplicates are no good to anyone. Redundancy messes up your queries, creates potential update anomalies and causes incorrect results.
There is no sensible reason to add another row if a customer rents a video twice. If the table had a date in it then that might make sense because you would be recording extra information and by implication the date would form part of a compound key.
Adding a "generated" key won't solve the problem of redundancy unless you enforce the compound key as well. That's why natural keys are important. If you design tables on the principle that a table needs just one key and that just any key will do then the results are unlikely to be fit for purpose.
November 12, 2010 at 1:41 pm
I have to agree with David on this one. An identity column on that table doesn't make sense except as a lazy clustered index (and rental date-time would almost certainly be better for that).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply