Table that relates to multiple tables?

  • Ok, foreign keys are kids stuff. I think i've got the concept down, and am ready to eat crow for this arrogance.

    But let's say a table is envisioned, that could conceivably be related to many other tables - for example, a Media table that would store records of media that are added to the system, and each media could be related to almost anything else in the system - a table called Items, a table called Employees, a table called WebPages - the list could go on.

    The point is that it becomes unwieldy at some point to add a foreign key column to the Media table for every table that it could be related to, no? Is it ever done where, instead of a specific foreign key to each table, the relationship between the Media records and the other tables are stored with two columns in the Media table, like so:

    TableName VarChar

    RowID BigInt

    So that when a record is inserted into the Media table, the TableName could be specified as 'Employees' and the RowID as the EmployeeID that the Media record is associated with? This seems much more flexible, but the database wouldn't be aware of any foreign key relationship, and i don't know how this Media record could then be extracted along with the Employee record it is associated with, at least with the SQL i've been exposed to..

    Would love to hear some wisdom about this sort of situation, thanks for your time!

  • For data integrity issues it is much better to explicitly define the column(s) and foreign key(s).

    In the scenario you mention you would have enforce RI using triggers which is much less efficient and prone to errors or you would have the possibility of "orphaned" rows. You also would have difficulty writing select statements as you would have to include logic in an y joins to determine what table to join to. Also, how would handle a situation where a media row is related to both an employee AND and Web Page?

  • As for the last question, i guess i over-simplified the example; i would handle Media rows being related to multiple other records with a relation table, just like any other one-to-many relationship, no? It would just be that relation table that would then hold a record of what table it relates to. So now we have:

    Media

    ----------

    MediaID

    Filename

    Filesize

    ...

    MediaRel

    ----------

    MediaRelID

    MediaID

    TableName

    RowID

    Select statements are a bit more complex, i'll agree, but only a bit - when joining from, say, the Employees table to its media, the join would just have to look like:

    INNER JOIN MediaRel

    ON Employees.EmployeeID = MediaRel.RowID

    AND MediaRel.TableName = 'Employees'

    INNER JOIN Media

    ON Media.MediaID = MediaRel.MediaID

    We already need to know the name of the Employees table in the query in some sense, it just depends on the building of the query, no? How much negative effect will adding that check for the table name have on the performance of the join in the query?

    You mention using triggers to maintain referential integrity in this sort of scenario, what would that look like? I can also envision a scenario when orphaned rows may be acceptable, say for an auditing table that keeps track of updates and deletes of records in many other tables?

    Really appreciate the feedback, the database is a powerful and mysterious entity.. 🙂

  • The short answer is ... neither of the ways you're describing ... is the right way.

    You're right, you wouldn't put a whole bunch of fields that are 'potential' foreign keys to other tables into the Media table. In fact, that way, if I'm understanding you correctly, just plain ol' wouldn't even work. At least, not without your media table containing duplicate rows for the same piece of media. Which really sorta makes it no longer proper 'media' table.

    As for the other way you describe, your 'table of tables and keys' method ... it might 'work' in some senses of the word, but believe me, it has no advantages, and a whole host of disadvantages.

    It'll be slower to run, slower to code, harder to maintain, and you won't be able to make use of the DB engines inherent ability to maintain referential integrity ... one of the biggest raison d'etres for using relational databases in the first place.

    The way you're describing it, the way I'm understanding you, what you'd want to do is set up many-to-many relation tables.

    Something like this:

    tblMedia

    media_id int identity pk

    medianame varchar(40)

    etc ...

    tblEmployee

    employee_id int identity pk

    employeename varchar (40)

    tblEmployeeMedia

    media_id int references tblMedia(media_id)

    employee_id references tblEmployee(employee_id)

    primary key(media_id, employee_id)

    I believe that is what would be 'the proper way' to do what it seems you are suggesting 😀

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply