CODEWITHSUNDEEP
mysqldatabaserelational-database
Leon
Leon

Reputation: 1881

Database: How do I relate an ID to EVERY table? (many to many to many!)

I'm designing a relational database, and I would like to know best practice for the following problem:

I have a table called "links", which contains URLs.

Most of my other tables relate to objects which appear throughout my site, such as "news", "music", "banners" - and these are joined with one-to-many or many-to-many relationships in the usual way. So far so good...

The links table (link_id) can apply to almost ANY of my objects. For example, "news" could have many "link_id, one "link_id" could apply to many "music". Many "banners" could share many "link_id" - links are important to my site!

I can think of two solutions for this :

1) create a junction table for each new object to which "link_id" applies, thus creating dozens of new tables, and adding new ones each time I add an new object to my application. 

>##*Table: links_to_news*##

>##*Table: links_to_music*##

>##*Table: links_to_banners*##

> etc... etc... etc...

2) reference the table name in a single new table, and create a join, ie: 

>##Table: links##

>| link_id | link_url |

>##*Table: links_to_tables* ##
>*| link_id | a_table_name | id_within_that_table |*

> ##Table: news##

>| news_id | news_text | 

> ##Table : music ##

>| music_id | music_text |

Both of these methods seem a little bit clunky to me. So is there a correct way to do what I need? Thanks.

Upvotes: 0

Views: 58

Answers (1)

rlanvin
rlanvin

Reputation: 6277

You are trying to implement a polymorphic relation/assocation. First option is difficult to maintain/extend. Second option is more flexible, but doesn't allow foreign keys, which might not be desirable.

In this case there is a third solution you need to consider. You will need a "supertable" storing one global unique ID for all objects that can have links. The table could be called linkables for example. It only has one column (a unique auto increment ID). Each table (news, music, etc.) has a linkable_id column, which is a foreign key. In OOP terms, you're making your News, Music (...) classes inherit from an (abstract) Linkable class. Then you create a junction table (linkable_id, link_id).

Basically, you are moving the "link" relationship to a "parent" table, thereby removing the polymorphism.

Suggested reads:

Upvotes: 1

Related Questions