May 7, 2008 at 11:20 am
Let's say I have three tables with the following table names and columns:
Opportunity
opportunityID [will be identity column]
customerID
generalData1
generalData2
Business
customerID {FK to Opp}
businessData1
businessData2
Individual
customerID {FK to Opp}
individualData1
individualData2
I am attempting to illustrate that an opportunity has a customer, which can be either a Business or an Individual (but not both). If my terminology is correct - this is in fact a "Has a" relationship.
A clarifying question about best practices before I continue.
1) Is it better to unify customerID between the Business and Individual tables, or give Business bID and Individual iID? [Note: This ID only serves as a simple primary key in each of these tables]
When I create a "customer" - I would like to ensure id uniqueness across the two tables (business and customer) if what I'm doing is a best practice. Any thoughts you have on making that a reality are appreciated.
My issue is as follows: Assuming I've managed that, when selecting opportunities, what methods do I have for determining which "type" of customer the opportunity holds, given only the opportunity id?
Apologies for the long-winded explanation, but in the absence of expertise and a solid grasp of terminology, it seems best to be as clear as possible using plain english.
-Devin
May 7, 2008 at 12:13 pm
Hmmm. Since the columns are the same across the two, I'd create one table, and simply have another column indicating "type" whether it be a numeric value(1,2) or a char(1) value ('B' or 'C') you can easily filter on that if you want one type or the other, then you save yourself crazy joins and such trying to maintain uniqueness.
Hope this helps.
Just my two pfennigs.
May 7, 2008 at 12:51 pm
You have a couple of options.
One is to roll the business and customer tables together into one table, with a type column that indicates which ones are which. If the columns in the two are the same, that will work.
Assuming the columns in businesses and customers aren't the same (they most likely aren't), then having two columns in Opportunities, CustomerID and BusinessID, and a FK for each column to the relevant table, and a check constraint that says one of these must be null but not more than one.
The second solution is what I'd be inclined to use, based on what little I see of your database here.
Is that clear enough, or should I provide some sample code?
- 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
May 7, 2008 at 9:30 pm
Both of you have provided excellent options to explore - thanks for your time!
-Devin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply