Search code examples
databasepolymorphic-associations

Database best practices

I have a table which stores comments, the comment can either come from another user, or another profile which are separate entities in this app.

My original thinking was that the table would have both user_id and profile_id fields, so if a user submits a comment, it gives the user_id leaves the profile_id blank

is this right, wrong, is there a better way?

Solution

  • In the past I have used a centralized comments table and had a field for the fk_table it is referencing.

    eg: 

    comments(id,fk_id,fk_table,comment_text)

    That way you can use UNION queries to concatenate the data from several sources.

    SELECT c.comment_text FROM comment c JOIN user u ON u.id=c.fk_id WHERE c.fk_table="user"
UNION ALL
SELECT c.comment_text FROM comment c JOIN profile p ON p.id=c.fk_id WHERE c.fk_table="profile"

    This ensures that you can expand the number of objects that have comments without creating redundant tables.