sql server - Best approach to avoid Too many columns and complexity in database design -

sql server - Best approach to avoid Too many columns and complexity in database design -

inventory items :

paper size ----- a0 a1 a2 etc paper weight ------------ 80gsm 150gsm etc paper mode ---------- colour bw paper type ----------- glass silk normal tabdividers , tabdivider type -------- binding , binding types -- laminate , laminate types --

such inventory items , these needs stored in invoice table

how store them in database using proper rdbms.

as per sentiment each list master table , retrieval joins. may little bit complex adding many tables database.

this normalisation having bit of problem when storing info against invoice. causing many columns in invoice table.

other way putting of them 1 table more columns , each row combination of them.. (hacking algorithm 4 list 4 items on 24 records have reference id).

which 1 think best , why!!

your initial thought correct. , claiming 4 tables "a little bit complex" and/or "too many tables" shouldn't doing database work. rdbms's designed (and tuned) do.

each of these 4 items individual property of can't put, is, table merges them. had thought, start with:

papersize paperweight papermode papertype

these lookup tables , hence should have non-auto-incrementing id fields.

these used foreign key fields main paper-based entities.

or if can exist in combinations, there need relationship table capture/manage valid combinations are. 4 paper "properties" still separate tables foreign key relationship table. people set separate id field on relationship table uniquely identify combination via single value. personally, wouldn't unless there technical requirement such replication (or other process/feature) required each table had single-field key. instead, create pk out of 4 id fields point paper "property" lookup tables. 4 fields still go paper-based entities. @ point main paper entity tables same if there wasn't relationship table, difference beingness instead of having 4 fks of single id field each, 1 each of paper "property" tables, there single fk of 4 id fields pointing pk of relationship table.

why not jam single table? because:

it defeats purpose of using relational database management scheme flatten out info non-relational structure. it harder grow construction on time it makes finding paper entities of particular property clunkier it makes finding paper entities of particular property slower / less efficient maybe other reasons?

edit: regarding new info (e.g. invoice table, etc) wasn't in question when writing above, should abstracted via product/inventory table capture these combinations. referring main paper entities. invoice table refer productid/inventoryid (just example) , product/inventory table have these paper property ids. don't see why these properties in invoice table.

edit2: regarding ids of "property" lookup tables, 1 reason should not auto-incrementing values should taken enums in app layer. these lookup tables means of providing "data dictionary" database layer can have insight these values mean.

sql-server database sql-server-2008