Primary key for every table

Hello,

I would like to ask it is good to have a primary key for every table?

Here is my current data.

date - time - code - NG1 - NG2 - NG3 - NG4 - NG5 … NG200
7/1/2017 - 10:00am - C123 - 0.12 - 12.01 - 1 - 12- 3 … 55

My structure
main table
columns: id - date - time - code

NG table
columns: id - ng_name

data table
columns: id - main_id - ng_id - value

id- auto increment.

Question?
do I need id on data table?

Thanks.

Roggie

No. You don’t need an id for the data table. You can make the main_id and ng_id columns a composite unique index.

If your NG names are always just NGx, you don’t need the NG table either, just use the x value as the ng_id in the data table.

Thank you phdr for your reply.

adding additional data.

date - time - code - NG1 - NG2 - NG3 - NG4 - NG5 … NG200
7/1/2017 - 10:00am - C123 - 0.12 - 12.01 - 1 - 12 - 3 … 55
7/2/2017 - 11:00am - C122 - 0.32 - 14.06 - 6 - 72 - 6 … 44
.
this can reach up to 2k row average every unit and there are 9 unit. That is 18k row a day.

Data table can have up to 3 million rows a day. Trying to save bytes that is why I made a new table for NG’s
NG1, NG2, up to NG200 are only alias. the real name are sample as follows. “New Sample”, “Old Sample”, “X7120-M”, “EXP1069-7-M”

My additional question. Currently there is no requirement of editing the the data table. If I removed the PRIMARY KEY on the data table and we required to edit the data table. How can I edit it without the PK?

simple command I use to edit a row.
UPDATE data_table SET row1=“a value” WHERE id=5

What will I use on WHERE statement if id(PK) is removed? Thanks in advance

For editing the data, you would use the two values making up the composite unique index -

UPDATE data_table SET value=? WHERE main_id=? AND ng_id=?

Thank you phdr for such bright idea.

Sponsor our Newsletter | Privacy Policy | Terms of Service