locked
Redesign multiple tables using same column for foreign key RRS feed

  • Question

  • I came across a table that that has foreign keys, for different tables, in the same column, depending on what was being saved.  Another column has a foreign key that tells what table to join against for that row. A distinct shows there are 12 different tables using the same column.

    How do I go about redesigning this structure to be a relational table?
    Do I make 12 look up tables then do a join off all of them? Or maybe some sort of CASE WHEN?

    Parent
    Id

    ParentTable1Link
    Id
    ParentId
    Table1Id

    SELECT P.Id
    FROM Parent P
    JOIN ParentTable1Link PT1L ON P.Id = PT1L.ParentId
    JOIN ParentTable2Link PT1L ON P.Id = PT2L.ParentId
    JOIN ParentTable3Link PT1L ON P.Id = PT3L.ParentId

    Wednesday, April 21, 2010 11:58 PM

Answers

  • So I am not quite clear about what the parent table represents here.  Is this a subclass sort of thing, where the Parent represents the generic object? Like

    Create table animal
    ( AnimalId int primary key,
      AnimalType ...)

    Create table dog
    ( AnimalId int primary key)

    Create table cat
    ( AnimalId int primary key)

    If so, then it seems quite appropriate to do this.  The real chore the is dealing with:

    >>The database stores data for customers, vendors, employees, offices, restaurants as well as different groups.

    This way the user can click on a name or group and it will pop up with the information without doing any searches. i.e They can have their favorite restaurant as well as vendors and customers they call often on the same screen<<

    The way to do this is to push all common attributes into the superclass that you need that allows you to deal with the objects in a generic fashion, and keep only the specific information in the child tables that are essential to the utilization when dealt with independently.

    So take customers, vendors, employees, offices.  Whatever is common that you want to deal with generically, (name? address?) you would put into the (for lack of a better name) gelatainousBlog table. (You may not be able to normalize columns to the extent desired there, particularly if you have too jelloish of a supertype, but it is doable with some work and planning).

    In the child table, you would put the employee's employee number, salary, whatever you need to make it specific...

    In the animal scenario, consider a pet store. You sell animals, but you have specific requirements/upsell information for the individual animal types.  When you do inventory, it would be at the animal level, but each animal might have a row in the child table with specific attributes that are specific to the animal type...

    Or I might be missing the point... Could you post a more specific table design?


    Louis

    Friday, April 23, 2010 5:22 AM
  • If the presence of these 12 tables are justified, then you may use table inheritance to design it properly by creating a ParentTable that references Parent and create your 12 tables to inherent from ParentTable.

    For more details on how to accomplish table inheritance refer to:

    http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

    Thursday, April 22, 2010 9:21 AM
  • From the sound of it, i'd say to use 12 different COLUMNs Then it would be easy to do a 12 way join each time.
    Thursday, April 22, 2010 3:28 PM
    Answerer

All replies

  • If the presence of these 12 tables are justified, then you may use table inheritance to design it properly by creating a ParentTable that references Parent and create your 12 tables to inherent from ParentTable.

    For more details on how to accomplish table inheritance refer to:

    http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

    Thursday, April 22, 2010 9:21 AM
  • From the sound of it, i'd say to use 12 different COLUMNs Then it would be easy to do a 12 way join each time.
    Thursday, April 22, 2010 3:28 PM
    Answerer
  • After reading through the article I am not sure if this is the way to go.

    It works nice if you know what data you want. i.e. all students.

    If you want to return a record set of all people named ‘Bob’ no matter what type they were, you would still need to do outer joins on all tables to get the data you were looking for.

    Thursday, April 22, 2010 4:56 PM
  • If all your tables have the same datatype and structure so you cannot distinguish them already by any means then you should represent them in one table and that is it.

    If you need to get more helpful answer then clarify the reasons behind your current design, why you need those 12 table in the first place?

    Thursday, April 22, 2010 5:16 PM
  • The database stores data for customers, vendors, employees, offices, restaurants as well as different groups.

    The user is allowed to select names from the side menu and add it to their “quick view” screen.

    This way the user can click on a name or group and it will pop up with the information without doing any searches. i.e They can have their favorite restaurant as well as vendors and customers they call often on the same screen.

    When it gets saved, it all goes back to one table with the unique id and table the unique id came from.

    Adding 12 columns seems wrong. What if down the road there is a change? I will end up needing to add more columns to the table.

    Thursday, April 22, 2010 6:01 PM
  • So I am not quite clear about what the parent table represents here.  Is this a subclass sort of thing, where the Parent represents the generic object? Like

    Create table animal
    ( AnimalId int primary key,
      AnimalType ...)

    Create table dog
    ( AnimalId int primary key)

    Create table cat
    ( AnimalId int primary key)

    If so, then it seems quite appropriate to do this.  The real chore the is dealing with:

    >>The database stores data for customers, vendors, employees, offices, restaurants as well as different groups.

    This way the user can click on a name or group and it will pop up with the information without doing any searches. i.e They can have their favorite restaurant as well as vendors and customers they call often on the same screen<<

    The way to do this is to push all common attributes into the superclass that you need that allows you to deal with the objects in a generic fashion, and keep only the specific information in the child tables that are essential to the utilization when dealt with independently.

    So take customers, vendors, employees, offices.  Whatever is common that you want to deal with generically, (name? address?) you would put into the (for lack of a better name) gelatainousBlog table. (You may not be able to normalize columns to the extent desired there, particularly if you have too jelloish of a supertype, but it is doable with some work and planning).

    In the child table, you would put the employee's employee number, salary, whatever you need to make it specific...

    In the animal scenario, consider a pet store. You sell animals, but you have specific requirements/upsell information for the individual animal types.  When you do inventory, it would be at the animal level, but each animal might have a row in the child table with specific attributes that are specific to the animal type...

    Or I might be missing the point... Could you post a more specific table design?


    Louis

    Friday, April 23, 2010 5:22 AM