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.