Reputation: 23
Suppose there is table Oil_man :
Oil_man_ID Component_A Component_B Raw_Data 645463 87 13 76,87,837,87272 844887 76 24 87,3938,39383,837383,3838,9,383,8378,14263,27263 764767 60 40 8487,8478
Now If I want to print all the values in Raw_data of a particular ID , I have to do some dirty hacks which is very confusing when the number of values in the Raw_Data start increasing rapidly . The values in Raw_data are random and have no restriction whatsoever except lengthwise ..
How should I design the database properly in such case ?The values in Raw_Data should be properly linked to Oil_Man_Id . Thank you
Upvotes: 0
Views: 55
Reputation: 108530
For repeating attributes, the normative pattern is a separate table, with a foreign key reference to the parent table. For example:
parent_table
oil_man_id component_a component_b
---------- ----------- -----------
645463 87 13
844887 76 24
child_table
oil_man_id attribute
---------- ---------
645463 76
645463 87
645463 837
645463 87272
844887 87
844887 3938
To avoid "duplicates", create a unique index on the combination of the FK and the attribute column. That can serve as the PRIMARY KEY of the table. (It's up to you whether you want to allow duplicates; you could add a surrogate PRIMARY KEY in either case.)
You could also add additional attributes, like an ordinal position (sequence), if the order of the attributes is important.
That's the normal pattern we'd follow if we want to be able to handle the values in Raw_Data as a set. Example definition for repeating attribute table, with UNIQUE constraint on attribute values:
CREATE TABLE child_table
( oil_man_id INT UNSIGNED COMMENT 'PK, FK ref parent_table.id'
, attribute INT UNSIGNED COMMENT 'PK'
, ordinal_position INT UNSIGNED COMMENT '1,2,3,...'
, PRIMARY KEY (oil_man_id, attribute)
, CONSTRAINT FK_child_table_parent_table FOREIGN KEY (oil_man_id)
REFERENCES parent_table (oil_man_id)
ON DELETE CASCADE ON UPDATE CASCADE
) Engine=InnoDB;
If there is never any need for MySQL to handle the individual components of Raw_Data as a set; there's no need to query if an individual value exists in Raw_Data, and there's no need to add or remove individual values, or return the individual values as separate rows... if MySQL will only ever treat this as a long anonymous string of characters, you could use a VARCHAR column to store it as a string. Or, if it's a really long string, (and the row size will exceed the maximum supported row size) you could use a TEXT
datatype.
Upvotes: 2