sqldatabaserelational-databaseparent-childname-value

Name value pair table vs parent child


I want to store about 100k rows of data, and all data some common field. All data have a category and other fields is base on category.

For example if data is in category 1, It had extrafield1 and extrafield2

I search and found two way for storing data.

1-Name value pair

Table1
ID    Name     Category   Field2           Field3
1     Name1    1          Value            Value
2     Name2    2          Value            Value

Table2 
ID    Table1_ID         Name           Value
1     1                 extrafield1    1
2     1                 extrafield2    2
3     1                 extrafield3    3
4     2                 extrafield4    4
5     2                 extrafield5    5

2-Parent Child table

Table1
ID    Name     Category   Field2           Field3
1     Name1    1          Value            Value
2     Name2    2          Value            Value

Tableforcategory1 
ID    Table1_ID         extrafield1    extrafield2     extrafield3
1     1                 1              2               3


Tableforcategory2 
ID    Table1_ID         extrafield4    extrafield5
1     2                 4              5   

So my question is when use method 1 and when use method 2.


Solution

  • Method 2 is generally preferred for a variety of reasons:

    And there may be other reasons.

    The first method -- which is called entity-attribute-value modeling (EAV) -- is definitely an alternative. It is mostly suitable in two situations:

    Sometimes a hybrid of these two methods is appropriate, with commonly used attributes being stored in a relational format and sparse attributes stored as EAV.

    There are alternative approaches, such as storing the values in a JSON or XML object. These are not generally recommended, but might be suitable in some databases under certain circumstances -- particularly when all attributes need to be treated as a single block and returned and set together.