CODEWITHSUNDEEP
database
Mateusz Wit
Mateusz Wit

Reputation: 485

Database design - referencing look-up table

I have a look-up table called ratings. It has two attributes: name (which acts as a primary key) and description. In my other table - books - I have a foreign key rating_name (which is a reference to rating.name). Is this a okay design or should I add id column for ratings table?

Upvotes: 0

Views: 43

Answers (2)

anujm
anujm

Reputation: 309

Your question essentially boils down to whether you want to use a surrogate key(one that's not related to the business domain) or a natural key(one that's related to the business domain). This is a highly debated question in the world of database design and this question(which has been appropriately marked as subjective) throws some light on the pros/cons of each.

Upvotes: 1

amaksr
amaksr

Reputation: 7745

More canonical design would be to have all 3 fields in your ratings table:

id NUMBER NOT NULL AUTO_INCREMENT
name VARCHAR
description VARCHAR

and "books" table should point to "ratings" by books.ratings_id = ratings.id. It would be better in case if at some point you will need to update names of your ratings.

Another reason is that many server frameworks assume that all your tables have numeric "id" field with primary key on it. So it will just save you some coding later.

To answer your question - you don't need another "id" if you already have a primary key field (ratings.name).

Upvotes: 1

Related Questions