sql - one-to-one or one-to-many: one table with hundred columns or moving columns to an extra table -

sql - one-to-one or one-to-many: one table with hundred columns or moving columns to an extra table -

i trying find best db design next problem.

i have 20000 info sets this:

1. id, name, color, width, xxxx ... 150 attributes 2. id, name, color, width, xxxx ... 150 attributes 3. ...

which means have 20000 entities , 150 attributes color, width etc each 1 of them.

i need these attributes , maybe 15 beingness used more others. beingness used in web application , has perform.

solutions thought about:

normalized 2 tables approach:

id, name , few "more important" attributes in 1 main table in table (one-to-one relation): id , other less of import attributes, every 1 of in different column

everything in 1 monster table:

id, name, color, width ...

normalized 2 table approach (one-to-many):

main table with: id , name in table (one-to-many relation): other table with: id, attr_name, value

i [3] not sure going perform if need lot of info because every "id" has 150 values. , have things like

select mt.id, mt.name, at.attr_name, at.value main_table mt inner bring together attr_table @ on at.id = mt.id , at.attr_name in ('width', 'color', 'a', 'b', 'c' .....) , at.id in (1,3,9...) order 1

having maybe 15-20 different values in "attr_name in (...)" not optimal. , if need 10-30 different datasets (which do), looks less appealing.

output of 200-300 lines , have normalize output in code.

[2] pretty dirty , simple not sure how perform. , having 150 columns in 1 monster table not optimal.

i on approach can lot of stuff in sql , not later in code like: attr1 - attr2 ... (like "max_width - width" or weight - max_weight/4).

[1] don't 1 because not clean have "some" attributes in 1 table , other attributes of same type in another

what best solution specific problem?

i found similar not same questions: best have hundreds of columns or split multiple tables? is improve have many columns, or many tables?

with few rows 20,000 have no uncertainty going total normalized. in sentiment much more rows still in instead of additional json's total new set of problems , weaknesses.

output of 200-300 lines , have normalize output in code

create view not have bring together @ every query

create view the_view select id, mt.name, at.attr_name, at.value main_table mt inner bring together attr_table @ using (id)

filter when selecting

select * the_view attr_name = 'color' , "value" = 'red' , attr_name = 'width' , "value" = '30' , id in (1, 3, 9)

150 columns suggest attributes list not stable. if create table 150 columns have altering table add together new columns. normalized approach flexible. create attributes @ simple adding row table.

there should 3 tables; main_table, main_table_attr_table , attr_table. main_table_attr_table n n connection between other two.

sql postgresql symfony2 database-design doctrine2