October 10, 2008 at 8:24 am
I have to setup a One to Many Relationship. For the sake of discussion lets say the tables are: Customers and SalesPerson. There is one Customer to Many SalesPersons. The rules for my company state that every customer must have at least one SalesPerson. On rare (and I mean rare) occassions a Customer can have more then one SalesPerson. The other rule was that a SalesPerson can only belong to one customer (this of course is a fictious example).
Normally I would create a:
Customer Table with a CustomerID field as the PrimaryKey
SalesPerson Table with the SalesPersonID as the PrimaryKey
CustomerSalesPersonXref with the CustomerID and SalesPersonID as the PrimaryKey.
Given the business rules above would it be a bad idea to include the SalesPersonID in the Customer table?
Thanks
October 10, 2008 at 8:28 am
I think you have a many to many relationship. Do your sales people have more than 1 customer? Or would they really only have 1 customer. If it's a many to many, you need the junction table.
October 10, 2008 at 8:52 am
smithsjt has it right in that if you have the junction table, you have a many to many.
For a one to many customer to salesperson, you really have CustomerID in the SalesPerson table. Not the other way around. If you have SalesPersonID in the Customer table, you are saying one salesperson per customer.
I would leave it the way you have it. You can add logic or a trigger to ensure one customer per salesperson, but this is one of those business rules you have to expect to change. Having a salesperson only work with one customer is rare. And it often changes if you get a really small customer and you make a salesperson then have two customers.
October 10, 2008 at 9:49 am
Sorry if I was confusing. I was not sure how to phrase the question. So I came up with a fictious example. Based on the responses I got my fictious example only made things worse. What follows is another (hopefully better) fictious example that explains my question.
I have a Master/Detail relationship. My business rules state that there must be at least one Detail Item for each Master Item. From experience I know that 99% of the time that there actually is only One Detail Item for each Master. It is very rare that a Master Item will have more then one Detail Item.
The Detail Table Has the following Fields:
MasterID
Sequence Number
FieldA
FieldB
Since I know that I always have to have one Detail Item for each Master and that almost all of the time that there is only one Detail item, would it be a good or bad idea if the Master table contained the fields FieldA and FieldB? This way I would only have to concern myself with the Detail table on rare occassions?
Thanks
October 10, 2008 at 2:31 pm
meichner (10/10/2008)
...Since I know that I always have to have one Detail Item for each Master and that almost all of the time that there is only one Detail item, would it be a good or bad idea if the Master table contained the fields FieldA and FieldB? This way I would only have to concern myself with the Detail table on rare occassions?
Personally, I don't think it's good design, but that's just my opinion. Were you just looking to save the cost of the join between the master table and the detail table? If you made the clustered index on the detail table be the MasterID and the SequenceNumber, it should be fairly efficient. If you did put FieldA and FieldB in the master table, what mechanism would you use to be able to determine if your query needs to look in the detail table? I'd think that those queries would be much more inefficient than just doing the simple join to begin with.
October 10, 2008 at 3:26 pm
meichner (10/10/2008)
I have a Master/Detail relationship. My business rules state that there must be at least one Detail Item for each Master Item. From experience I know that 99% of the time that there actually is only One Detail Item for each Master. It is very rare that a Master Item will have more then one Detail Item.
There is no such word as "rare" in Relational Database Design. This relationship is either One-to-Many or it isn't. Which is it?
Since I know that I always have to have one Detail Item for each Master and that almost all of the time that there is only one Detail item, would it be a good or bad idea if the Master table contained the fields FieldA and FieldB?
That would be a Bad idea. Very, very Bad. That would in fact be "Not Relational At All".
This way I would only have to concern myself with the Detail table on rare occassions?
This is exactly wrong. Your code will, in fact, have to concern itself with the Detail table every single time because until it checks, it does not know how many Detail records there are.
It is much more easy, effective and efficient to just always assume that there could be multiple Detail records, instead of writing special checks and special-case code to try to avoid this possibility. This is one of the essential truths of the Relational approach.
Bite the bullet and do it right.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 10, 2008 at 4:28 pm
Thanks for the clarification and I'm with Barry. Do not do this as it is asking for issues.
If this were an extremely busy system and the were performance problems from the join, I might think this is OK, but you'd also have to note in the master record if there is more than one detail row, because then you'd have to do the join.
Index on masterID, fieldA, fieldB and this will cover and join quickly.
October 10, 2008 at 6:32 pm
I want to thank everyone for the advice. In the past when faced with the same situation I handled things in a more relational way. The idea that I mentioned occurred to me today while I was creating my Master/Detail tables. I thought the idea was a bad one, but I wanted to get some expert advice before rejecting the idea.
Thanks again
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply