SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A database design question


A database design question

Author
Message
meichner
meichner
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2361 Visits: 640
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
smithstj
smithstj
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 405
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.



Steve Jones
Steve Jones
SSC Guru
SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)

Group: Administrators
Points: 282875 Visits: 19914
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
meichner
meichner
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2361 Visits: 640
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
Chris Harshman
Chris Harshman
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19912 Visits: 5518
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.
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68886 Visits: 9519
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Steve Jones
Steve Jones
SSC Guru
SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)

Group: Administrators
Points: 282875 Visits: 19914
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
meichner
meichner
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2361 Visits: 640
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search