Best way to link a table to 2 different keys ?

Question

Hi,

I'm designing a mySQL DB and I'm having the following issue:

Say I have a wall_posts table. Walls can belong to either an event or a user. Hence the wall_posts table must references either event_id or user_id (foreign key constraint).

What is the best way to build such a relationship, considering I must always be able to know who the walls belong to ... ?

I've been considering using 2 tables, such as event_wall_posts and user_wall_posts so one got an event_id field and the other a user_id one, but I believe there must be something much better than such a redundant workaround ...

Is there a way to use an intermediate table to link the wall_posts to either an event_id or a user_id ?

Thanks in advance,

Edit : seems there is not a clear design to do this and both approach seem okay, so, which one will be the fastest if there is a lots of data ?

Is it preferable to have 2 separates table (so queries might be faster, since there will be twice less data in tables ...), or is it still preferable to have a better OO approach with a single wall_posts table referencing a wall table (and then both users and events will have a unique wall_id`)

Answer 1

Why is it redundant? You won't write code twice to handle them, you will use the same code, just change the name of the table in the SQL.
Another reason to take this approach is that some time in the future you will discover you need new different fields for each entity.

Answer 2

What you're talking about is called an exclusive arc and it's not a good practice. It's hard to enforce referential integrity. You're probably better off using, in an object sense, a common supertype.

That can be modelled in a couple of ways:

1. A supertype table called, say, wall. Two subtype tables (user_wall and event_wall) that link to a user and event respectively as the owner. The wall_posts table links to the supertype table; or
2. Putting both entity types into one table and having a type column. That way you're not linking to two separate tables.

Answer 3

A classical approach to this problem is:

• Create a table called wall_container and keep properties common to both users and events in it
• Reference both users and events to wall_container
• Reference wall_posts to wall_container

However, this is not very efficient and it's not guaranteed that this wall_container doesn't containt records that are not either a user or an event.

SQL is not particularly good in handling multiple inheritance.

Answer 4

Your wall and event has their own unique IDs .. right?? then their is no need for another table . let the wall_post table have a attribute as origin which will direct to the record of whatever the record is event's or users. '

If the wall and event may have same ID then make a table with three attributes origin(primary), ID number and type. There ID number will be what you set, type defining what kind of entity does the ID represent and origin will be a new ID which you will generate maybe adding different prefix. In case of same ID the origin table will help you immensely for other things to other than wall posts.

Answer 5

Go for the simplest solution: add both an event_id and a user_id column to the wall_posts table. Use constraints to enforce that one of them is null, and the other is not.

Anything more complex smells like overnormalization to me :)