CODEWITHSUNDEEP
mysqlsqldatabase
user3201993
user3201993

Reputation: 23

How to design database for handling multiple values in a column related to a single primary key?

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

Answers (1)

spencer7593
spencer7593

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

Related Questions