0

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.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Leon
  • 1,851
  • 3
  • 21
  • 44
  • 2
    Search for "polymorphic associations". FYI there is a third solution, creating a "linkeable" table, with nothing but a unique ID. Your tables "news", "music", etc. will all have a unique "linkeable_id". Then you create a junction table `(linkeable_id, link_id)`. More info here: http://stackoverflow.com/questions/922184/why-can-you-not-have-a-foreign-key-in-a-polymorphic-association/922341#922341 – rlanvin Aug 20 '15 at 11:13
  • are objects 'news', 'music', 'banners' etc contained in single table? Or you have different tables for each object? – aadi Aug 20 '15 at 11:20
  • ^different tables for each – Leon Aug 20 '15 at 11:21
  • re. the "linkable_id" option - each entry could have many of these... I'm not sure if thats the correct way... – Leon Aug 20 '15 at 11:23
  • How about bit customized version of your proposed solution 2: Make new table for links ( linkid | url ) and then use it's primary key as Foreign key in all the news, banners, music tables? – aadi Aug 20 '15 at 11:26
  • 1
    @Leon No, each entry (news, music, etc.) only has one "linkable_id". But each "linkable" object can have many link_id in the junction table. – rlanvin Aug 20 '15 at 11:29
  • OK, so it would appear to be a polymorphic association, thanks for making me aware of that. I see what you are saying! Each "linkable" object is an entity in its own right. Yes, I think that will work. Thank you. – Leon Aug 20 '15 at 11:37

1 Answers1

1

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:

Community
  • 1
  • 1
rlanvin
  • 6,057
  • 2
  • 18
  • 24