StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is called <a href="http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model" rel="nofollow noreferrer">EAV</a>.<br> You can find some questions about EAV and SQL Server here on SO:</p> <ul> <li><a href="https://stackoverflow.com/q/1336449/6884">EAV over SQL Server</a></li> <li><a href="https://stackoverflow.com/q/192892/6884">Performance of large EAV/open schema systems on SQL Server</a></li> </ul> <p>It's a subject where lots of different people will have different opinions.<br> Some will say that you should stick with <code>Product_%ProductTypeId%</code> tables, and some will say that your <code>ProductTypeColumns</code> table is the better way.</p> <p>Basically, I'm in the <code>ProductTypeColumns</code> camp.<br> We are using something similar at work as well:<br> Our table contains attributes for order items (over a million order items and over 250 million attributes) and we are using it for nearly ten years now.<br> Disadvantages: no type safety (as you already mentioned) and <a href="https://stackoverflow.com/a/231681/6884">querying can be a bit complicated</a>.</p> <p>But for us, it's the only possible way because we have over 1000 attributes, over 100 products and each product can have 100 to 300 attributes. With dimensions like these, an attribute table is the only possible way.<br> But it's probably not the best solution for everyone and I don't know if it's the best solution for you. </p> <p>If your number of products and special attributes is not that large, maybe you can get away with your first suggestion (<code>Product_%ProductTypeId%</code> tables).<br> That would definitely be easier to query, but maybe it's not an option if there are too many possible combinations of products and attributes.</p> <p>As often, it depends.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload