Database design, How to reduce the data redundancy for different column

Question

I have an existing table which requires different columns for different record. do I need to split it into multiple tables to reduce the redundancy?

Table1:

productId   typeId  commonAttr1 commonAttr2 attrA1  attrB1 attrB2
_________________________________________________________________
1           1       1Avalue1    1Avalue2    value1      
2           1       1Avalue1    1Avalue2    value2
3           2       2Bvalue1    2Bvalue2            value3  value4
4           2       2Bvalue3    2Bvalue4            value5  value6

The "attrA1" is only for recored with typeId=1, "attrB1" and "attrB2" are only for records with typeId=2, "commonAttr1", "commonAttr2" are for both.

There might be 3 options:

1. Do not change. accept the data redundancy.

2. Move "attrA1" to new table2, move "attrB1","attrB2" to new table3.It'll be like:

Table1:

productId   typeId  commonAttr1 commonAttr2
___________________________________________
1           1       1Avalue1    1Avalue2
2           1       1Avalue1    1Avalue2 
3           2       2Bvalue1    2Bvalue2           
4           2       2Bvalue3    2Bvalue4

Table2:

productId   attrA1
__________________
1           value1
2           value2

Table3:

productId   attrB1   attrB2
___________________________
3           value3   value4
4           value5   value6

3.Create a new table to store the column as Key/Value

Table1:

productId   typeId  commonAttr1 commonAttr2
___________________________________________
1           1       1Avalue1    1Avalue2
2           1       1Avalue1    1Avalue2 
3           2       2Bvalue1    2Bvalue2           
4           2       2Bvalue3    2Bvalue4

Table2:

productId  typeId   key       value
____________________________________
1          1        attrA1    value1
2          1        attrA1    value2 
3          2        attrB1    value3           
3          2        attrB2    value4
4          2        attrB1    value5
4          2        attrB2    value6

Which option is better or is there any other solution? Personally I suppose option 2 is better.

It depends on why you want to do this schema refactoring. What is the issue you are facing with the existing table ? — objectNotFound, Jul 08 '16 at 02:23
[Normalization](https://en.wikipedia.org/wiki/Database_normalization) is what you're attempting and it's always a good idea to keep in mind when designing a DB (although you can overdo it). I'd go with option 2 too, but introduce another table just for the common attributes with it's own id column and your actual transaction table would just have a productId, TypeId and a CommonAttrId — Ash, Jul 08 '16 at 02:31
Thanks, Ashwin, for option2, I kept table1 with the commonattr columns, just move the others to table2 and table3. — winston.xie, Jul 08 '16 at 02:48
@AshwinNair Restructuring involving NULLs or EAVis not normalization, which is about replacing a table by projections of it that inner join back to it. — philipxy, Jul 08 '16 at 02:57

score 0 · Answer 1 · answered Jul 10 '16 at 16:24

0

4 One table with productId, typeId, commonAttr1, commonAttr2, json

where that last column is a JSON rendering of the other key-value pairs.

EAV (Entity-Attribute-Value) schema is problematic; sometimes #4 is a useful compromise. More discussion.

answered Jul 10 '16 at 16:24

Rick James

135,179
13
127
222

score 0 · Accepted Answer · edited May 23 '17 at 11:51

0

When you have a schema in which common attributes are stored in one table an the other ones are stored in their dependent tables it's called a model (base)type/subtype design (or a model for inheritance). In this stackoverflow question you would fine how to correctly implement it (in order to keep referential integrity)

Hope it helps

edited May 23 '17 at 11:51

Community

1
1

answered Jul 13 '16 at 19:04

rlartiga

429
5
21

Database design, How to reduce the data redundancy for different column

1. Do not change. accept the data redundancy.

2. Move "attrA1" to new table2, move "attrB1","attrB2" to new table3.It'll be like:

3.Create a new table to store the column as Key/Value

2 Answers2