Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question is an exact duplicate of:

In the following schema:

Collections
   Upvotes
Reports
   Upvotes
Reviews
   Upvotes

I'm tempted to have a single Upvotes table with a single "entityId" column that stores either the CollectionId, ReportId, or ReviewId. There will also be an enum for Type - storing either collection, report, or review.

The entityId will always be required, and the logic will always make sure that inserts enforce uniqueness across each type.

The benefits of this is that adding another type is just a matter of expanding the enum. Everything will live on a single table with no redundancy.

The cost seems to be the added complexity on the logic side, which is contained to the one place in my application logic that inserts new entities into the table.

Practically speaking, is there anything wrong with this approach? What would be some other reasons to avoid this?

share|improve this question
    
@yannishristofakis - I'm sorry, I meant polymorphic association. I have three entities whose FKs I'm treating as a single FK in the upvote table, with no referential integrity. I'll throw a diagram together shortly :) –  RobVious Dec 8 '13 at 18:16
add comment

marked as duplicate by RolandoMySQLDBA, Paul White, Kin, bluefeet, Max Vernon Dec 9 '13 at 23:28

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

I can't recommend polymorphic associations.

In my experience, this will lead to data consistency issues since you can't use a foreign key constraint in the schema. That means you're reliant on your application's writes to this part of the database being bug-free, and no one manually altering the data.

It can also make the queries which join Upvotes to >1 of the other tables more complicated/harder to understand.

share|improve this answer
    
You would you mind to giving us a better implementation. I was looking for something better a couple of days ago. –  yannis Dec 8 '13 at 19:56
1  
A separate upvotes table for each of collections/reports/reviews. –  Matt Ball Dec 8 '13 at 20:03
    
Thnk you Matt for your explanation. –  yannis Dec 8 '13 at 20:15
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.