creating relationship DB tables: what's the best plan
|
View:
New views
4 Messages
—
Rating Filter:
Alert me
|
 |
creating relationship DB tables: what's the best plan
by Scott Doc
::
Rate this Message:
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=17837.14401.6 |
|
|
Re: creating relationship DB tables: what's the best plan
by James Holmes-3
::
Rate this Message:
Number 2 is good normalisation practice and it's the right way to do it.
mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2008/11/20 Scott Doc <scott@...>: > 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! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:3151 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6 |
|
|
Re: creating relationship DB tables: what's the best plan
by Maureen-2
::
Rate this Message:
You want option two. The only downside in Access with this method is
you can't use the define relationship tool and paint the queries. But you can still do the selects with SQL. On Wed, Nov 19, 2008 at 8:56 PM, Scott Doc <scott@...> wrote: > 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. > 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:3152 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6 |
|
|
Re: creating relationship DB tables: what's the best plan
by Scott Doc
::
Rate this Message:
Thanks for the inputs!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:3154 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6 |
| Free embeddable forum powered by Nabble | Forum Help |
