Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    1

    using table name as foreign key?

    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.
    Attached Thumbnails Attached Thumbnails db_design.gif  
    Last edited by santoshamb; 08-01-02 at 03:07.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    Code:
    select stuff from member, linking, marketing
    union all
    select stuff from member, linking, dreamco
    union all
    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?

    ;o)

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Re: using table name as foreign key?

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •