PDA

View Full Version : using table name as foreign key?

santoshamb
08-01-02, 03:02
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.

Any help is greatly appreciated.
r937
08-02-02, 13:59
your design problem is common

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
union all
select stuff from member, linking, dreamco
union all
select stuff from member, linking, vendorsthis 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?

;o)

rudy
http://rudy.ca/
sco08y
10-29-02, 02:43
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".

Try this:

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.

In SQL:

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."