CODEWITHSUNDEEP
mysqlsql
Nick Shears
Nick Shears

Reputation: 1133

MySQL - Should every table contain it's own id/primary column?

I'm putting together a question and answers application - the answers are only going to exist as long as there is a question that relates to it.

So I've decided not to give the answers table it's own id column and have made the primary key a foreign key that relates to the question_id.

Questions table:

id | title

Answers table:

question_id | title

Should I keep it this way or give the answers table it's own id column?

Upvotes: 1

Views: 769

Answers (2)

Suvendu Shekhar Giri
Suvendu Shekhar Giri

Reputation: 1384

If there is possibility of multiple answers for a single question then it will be better to have a primary key on answer table too to identify each row uniquely if we get duplicate answers as follows

id | question_id | title 1 1 5 2 1 5 3 2 true

But, in case you are anticipating only a single answer for each question then it is better to merge it to the question table as both question and answer are directly dependent on a single primary key.

id | question | answer 1 quest 1 ? 5 2 quest 2 ? 5 3 quest 3 ? true 4 quest 4 ? null

I hope, this clarifies your doubt.

Upvotes: 1

MER
MER

Reputation: 1551

To expound a bit on the two valuable comments that have been made, in my experience, the following is the most effective set of rules to follow when defining a database schema (I will give reasons after):

  1. Create a Primary Key for each table
  2. Create a surrogate key to be that Primary Key
  3. When you have a one to many relationship (as you do in your questions & answers tables) include the PK from the one table (your questions table) in the many table (your answers table) NOTE: this is exactly as you have done it... except the answers table doesn't have it's own PK & surrogate key
  4. When there is a many to many relationship between two tables create a linkage/join/relationship table which has a one to many relationship to your two tables (meaning you put the Primary Key of each table into the relationship table as a foreign key to the two tables, respectively)

REASONS (in the same order):

  1. Primary key columns guarantee uniqueness for each row within the scope of the table itself (no other database object has to be involved & every row will be required to be unique). They also provide a default index in most databases, which speeds up table scans/queries. As, mentioned this effectively meets first normal form.
  2. I've found surrogate keys to be a powerful & effective way to simplify both database design & relationships between tables. If you aren't familiar please read here: http://en.wikipedia.org/wiki/Surrogate_key
  3. You have done this already, so I'm assuming you understand the benefits.
  4. This is here simply to provide an example of how using surrogate keys as primary keys in every table can help you as a database schema grows. If you need to add other tables in the future you won't have to spend as much time & effort figuring out how to join them you already have all the keys you need to easily create a join table (for instance, if you later add users to the mix... users can be the author of either a question or answer OR both... this could get a little harry if you attempt to associate the SAME value to both the question & answers tables independently... in fact it won't work)

Upvotes: 1

Related Questions