Search code examples
sqljpaforeign-keysprimary-keyebean

Best practice: foreign keys as primary keys or unique constraint

I'm implementing a shop using the Play! framework. Play uses Ebean as ORM. Right now I'm creating a rating system where a user may rate products. A user is allowed to rate a product only once. I was just wondering about best practice how to design the RATING table.

  1. Use user_id and product_id as primary key or
  2. Use a seperate id as primary key an set a unique constraint on the foreign keys user_id and product_id

What are the pros/cons?

User
---------------------
id (PK)
name
...
---------------------

Product
---------------------
id (PK)
name
...
---------------------

Rating
---------------------
user_id (FK)
product_id (FK)
rating
comment
PK(user_id, product_id)

OR:

Rating
---------------------
id (PK)
user_id (FK)
product_id (FK)
rating
comment
UNIQUE (user_id, product_id)

Thank you,

Nick

Solution

  • The first you listed because it is simpler. Both absolve the same task so why complicate your life? Read this: KISS