Hello, I have a design issue that I want some opinion on.
This is a contact database. There will be very different information for different types of contacts, ie vendors, marketing, etc. So each of these have thier own table. Then there is information common to all of the contacts, such as name, phone, ect. this information will also be put into its own table, call it the member table. A member can be a vendor, or a marketer. To tie the member table with the various vendor or marketing tables, a linking table will be created, with the memberid and the table name that it is associated with.
now the question I have is that I have never seen a "foreign key" that consists of a table name. So I am thinking that this is perhaps bad design. Unfortunately, I can't figure out any other way of doing it. I've included a diagram to help make sense of things.
the difficulty arises when you want to join a row from Member through the Linking table to... ?
with three separate tables, one strategy for selecting the right table is
select stuff from member, linking, marketing
select stuff from member, linking, dreamco
select stuff from member, linking, vendors
this works fine because only one of the subselects in the UNION will bring back any rows!
however, there's still a problem
the reason for having three different tables is usually because they have wildly different contents -- different number of columns, different types of columns, etc.
this implies that writing the UNION will not be as simple as above, since all three subquery result sets have to have the same number of columns and be datatype compatible (otherwise you get a syntax error in the UNION)
thus when the three tables are wildly different, you can only shoehorn them all together in the UNION by judicious use of CAST() and NULL in the subquery SELECT lists
this also makes the final result set difficult to "untangle"
your only other solution is to get a partial union from Member and Linking and then use IF/ELSE logic in the calling program to decide which third table to get
another way to approach the design problem is to redesign the three different tables into one table
there will probably be more columns, and some of them will make sense only for certain types of row
the "tableType" column that you now have in each of the three tables will still be in this combined table, but it will be a "rowType" column instead
this approach also requires some shoehorning, but i personally think this is the better way to go
it makes the join query simple
and when you think about it, the chances are that the three different tables aren't really wildly different
after all, they already had name, phone, etc. in common, eh?
Originally posted by santoshamb
now the question I have is that I have never seen a "foreign key" that consists of a table name. So I am thinking that this is perhaps bad design.
A foreign key, by definition, cannot be a table name.
A member can be a vendor, or a marketer.
Can a member be both? If not, just put the fields that are guaranteed common to all members (even if it's just an id and a name) in one table, and the other fields in the other tables as appropriate.
Unfortunately, I can't figure out any other way of doing it.
You're barking up the wrong tree with your "linking table".
Write down what a "member" is, in full sentence form. Example:
A member has an ID, a first name, and a last name.
This translates to SQL fairly easily:
CREATE TABLE Members (memberID INT PRIMARY KEY, firstName VARCHAR(50), lastName VARCHAR(50))
Now, for members who might be vendors:
A vendor has a member ID, a company s/he works for and a product s/he sells.
In this case, the "linking" is through a foreign key. That means that every Vendor must "reference" a Member. So the database has to ensure that every value listed for "member ID" in Vendors must also exist in Members.
CREATE TABLE Vendors (memberID INT PRIMARY KEY FOREIGN KEY REFERENCES Members, company VARCHAR(50), product VARCHAR(50))
Marketers, etc, would be similar to Vendors.
If you need to gather stuff together from these tables, read up on "outer joins."