Hello wise database experts, This is a hard thing to explain, but I'll try. I am using Access DB. I have several different tables of people, places, articles, photos, messages, comments, etc.. And as you would expect these tables are linked by 3rd party tables on things such as categories etc.
What I would like is to create a master relationship table that can DIRECTLY link any two things. Articles to people, articles to articles, photos to places, etc. So I need to create a table that takes their IDs as foreign keys. I can think of three methods: 1. LOTS of columns -- two for every table's ID (because they can also be related to each other) so: articleID1, articleID2, photoID1 ..... so any row would have two columns filled out, the rest null. 2. 4 columns. First two containing IDs, the second two with a key identifying the TYPE of ID ID1, ID2, typeof1, typeof2 so example row could be: 55, 23, article, photo 3. Make lots of tables. One for each relationship type. I don't want to do this one, too many, too messy. If I've got 6 things, that's 21 tables! My instinct is to go with #2 because it's simpler to maintain and create. Only 4 columns (ok, 5 including its own key) -- but I have a feeling this might be buggy and evil -- and might be easier for me but not the DB. I want to maximize efficiency, but also size and space (that's size of the Access DB - it's already quite big in MB). And it's not just a matter of proper foreign keys because even for option 1 each table has to be linked by 2 fields. Also I know that the queries later might be a handful -- but I think I have a handle on that. Thanks for your advice in advance. Scottdoc ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3150 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
