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.
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.