Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A database design question Expand / Collapse
Author
Message
Posted Friday, October 10, 2008 8:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 6, 2012 12:43 PM
Points: 215, 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

Post #583996
Posted Friday, October 10, 2008 8:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, April 9, 2010 8:14 AM
Points: 90, 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.




Post #584002
Posted Friday, October 10, 2008 8:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:28 PM
Points: 33,062, Visits: 15,174
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
Post #584030
Posted Friday, October 10, 2008 9:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 6, 2012 12:43 PM
Points: 215, 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

Post #584081
Posted Friday, October 10, 2008 2:31 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 1,849, Visits: 2,009
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.
Post #584244
Posted Friday, October 10, 2008 3:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #584272
Posted Friday, October 10, 2008 4:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:28 PM
Points: 33,062, Visits: 15,174
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
Post #584295
Posted Friday, October 10, 2008 6:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 6, 2012 12:43 PM
Points: 215, 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
Post #584313
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse