We're beginning a brand new project where we have to store product and several product characteristics inside a database. The sun's energy stack is MS SQL 2008 and Entity Framework 4. / LINQ for data access.

The items (and Items Table) are pretty straightforward (a SKU, manufacturer, cost, etc..). However you will find also many characteristics to keep with every product (think industrial icons). These could vary from color to certification(s) to pipe size. Best of luck might have different characteristics, plus some might have multiples of the identical attribute (Ex: Certifications).

The present proposal is the fact that we'll essentially possess a title/value pair table having a FK to the merchandise ID in each row.

A good example of the characteristics Table may seem like this:

ProdID     AttributeName     AttributeValue
123        Color             Blue
123        FittingSize       1.25
123        Certification     AS1111
123        Certification     EE2212
123        Certification     FM.3
456        Pipe              11
678        Color             Red
999        Certification     AE1111
...

Note: Attribute title may likely originate from a research table or enum.

Therefore the primary question here's: Is the very best pattern for doing something similar to this? The way the performance be? Queries depends on the JOIN from the product and characteristics table, and usually need many WHEREs to filter on specific characteristics - the most typical search is to look for a product with different group of known/preferred characteristics.

If anybody has any suggestions or perhaps a better pattern for this kind of data, please tell me.

Thanks! -Erectile dysfunction

This will probably be problematic for a few reasons:

  • Your entity queries is going to be more difficult to create. Changing the outcomes of individuals queries into something resembling a ViewModel when the time comes for presentation will probably be painful since it calls for a pivot for every product.

  • Being aware of what your datatypes is going to be will probably be tough when the time comes to see certain kinds of data. Are you currently thinking about storing this as strings? For instance, DateTimes hold more data compared to default .ToString() implementation creates towards the string. You are also likely to have issues by trying to keep floating-point values.

  • Your objects' data integrity reaches risk. You will see a temptation to place qualities that ought to be just characteristics of the primary product tables within this "bucket o' data". Maybe the look is going to be semi-sane to start with, however i guarantee you that whenever some time, folks will begin to just throw qualities within the bag. It'll then be very tough and also hardwearing . objects' integrity with your a loosely defined structure.

  • Your indexes will in all probability be suboptimal. Again think about a house that ought to be in your product table. Rather than having the ability to index on only one column, you'll now have to create a potentially large composite index in your "type" table.

  • Since you are apparently likely to get rid of proper datatypes and employ strings, the performance of range queries for number data will probably be poor.

  • Your table can get large, slowing down backup copies and queries. Rather than an integer being 4 bytes, you are going to need to store much more to have an integer associated with a size.

Easier to normalize the table inside a more "traditional" way using "IS-A" associations. For instance, you may have Pipes, that are a kind of Product, but possess a couple more characteristics. You may have Ovens, that are a kind of product, but possess a couple more characteristics still.

Should you genuinely have a normal database and many types of other qualities which are not likely to be susceptible to data integrity rules, you perfectly should consider storing data within an XML column. It's difficult to inform you exactly what the correct design option is unless of course I understand much more regarding your business.

IMO this can be a design antipattern. The siren song of the idea has attracted many a developer to the rocks of of the unmaintainable application.