February 4, 2009 at 6:24 pm
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!
February 5, 2009 at 6:34 am
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?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 5, 2009 at 10:00 am
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.. 🙂
February 5, 2009 at 11:34 am
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