sql server - Table Design - Wide Table vs. Columns as Properties -

i'm part of team architecting operational data store (ods) database, using sql server 2012, used of our analysts predictive modeling. ods contain manufacturing production data single product make.

we have hundreds of tables in ods. however, have single core table contain critical information (lifecycle info) each item manufactured (tens of millions each year). our product manufactured in manufacturing plant , spends 2.5 hours moving through various processes along production line. want store various, individual, pieces of manufacturing , post manufacturing information in core table. example piece of data might time product entered particular oven.

we have decision make on how architect table. can create wide table (many columns) or narrow table columns rows (as property values). have never designed , worked table structure narrow , columns treated rows in table.

i'd feedback on pros , cons of wide table vs. narrow table. following might useful in helping discussion:

number of products produced each year: several million (each of these product instances row in core table)

will table queried often: yes, often. parent many child tables.

potential number of columns (or row properties): 75 150+

if more information useful, i'd glad provide it.

wide tables, static properties

you tracking single product through well-defined manufacturing process. data model sounds static, , lend wide table many columns consistently populated data.

narrow tables, dynamic properties

if had many, many products lots of variation in manufacturing process, better suited narrow table, add new properties tracking.

difficult query narrow table

however, simple querying of narrow table can extremely difficult. example, if needed sort data property when property shuffled amongst 100+ other property rows? how rows form single "record" , sort record groups within result set?

flat tables simpler query

depending on how need view , analyze data, may find using pivot or crosstab queries. if that's case, why not flatten out storage table begin with?

or both

another option both: store data narrowly, , use transformation process flatten out ease of reporting. way can begin tracking new properties (just adding rows), , can work on getting reporting tables , transformation process updated utilize new data.