Search code examples
mysqldatabase-normalization

Storing key value where key repeats and using primary keys

I am in a situation where i have to store key -> value pairs in a table which signifies users who have voted certain products.

UserId    ProductID
1         2345
1         1786
6         657
2         1254
1         2187

As you can see that userId keeps on repeating and so can productId. I wanted to know what can be the best way to represent this data. Also is there a necessity of using primary key in here. I've searched a lot but am not able to find the exact specification about my problem. Any help would be appreciated. Thank you.

Solution

  • If you want to enforce that a given user can vote for a given product at most once, create a unique constraint over both columns:

    ALTER TABLE mytable ADD UNIQUE INDEX (UserId, ProductID);

    Although you can use these two columns together as a key, your app code is often simpler if you define a separate, typically auto increment, key column, but the decision to do this depends on which app code language/library you use.

    If you have any tables that hold a foreign key reference to this table, and you intend to use referential integrity, those tables and the SQL used to define the relationship will also be simpler if you create a separate key column - you just end up carting multiple columns around instead of just one.