Reputation: 485
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
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
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