\nMany of you have already heard the "hardware store" story. What's the best way to model products in a hardware store, where new items arrive at the hardware store each day. Each item has a "short list" of similar properties (e.g. UPC, price) and a long list of dissimilar properties (e.g. paint has color, type, amount and curtain rods have width, metal, etc). How to model the dissimilar properties for each item in relational table(s)?\n<\/p>\n
\nThis isn't as unusual of a problem as you might think, examples I've heard lately include:
\n Items in a directory system (like AD)
\n Readings for lab test results
\n Attributes for Sharepoint items\n<\/p>\n
\nI've always thought of the main contenders as:
\n1. Sparse tables – one per product
\n2. Sparse columns – 90% of the column values would be NULL
\n3. Model as XML – similar properties are subelements, sparse properties are attributes
\n4. Entity-attribute-value (EAV) – also known as open schema. A separate "properties" table with name-value pairs.\n<\/p>\n
\nEAV is one of the most popular solutions, even supposedly endorsed by standard schemas in some industries. Many relational purists detest EAV because its non-relational. It's main drawbacks are that the "name-value pair" table gets huge fast, with the corresponding lack of performance, the need for careful editing (color and colour would be two different attributes), and the fact that the "value" column of name-value must have a data type of nvarchar or SQL-variant.\n<\/p>\n
\nSQL Server 2005 added the PIVOT keyword. One use for PIVOT is the change the EAV tables into something that looks like sparse tables.\n<\/p>\n
\nI even had the opportunity to ask Joe Celko (no fan of EAV) which he prefers, trying to ease him towards the "model as XML" mechanism. He stood up for sparse tables or sparse columns.\n<\/p>\n
\nSQL Server 2008 will include support for sparse columns. You can designate a column as\n<\/p>\n
\nSPARSE in the DDL, like this:\n<\/p>\n
\nCREATE TABLE products (product_num int, item_num int, price decimal(7,2), …,
\n color char(5) SPARSE, width float SPARSE…)\n<\/p>\n
\nYou can have a huge number of sparse columns per table, although the number of non-sparse columns remains at 1024. In addition, SQL Server 2008 will support sparse indexes (aka filtered indexes) defined like:\n<\/p>\n
\nCREATE INDEX coloridx ON products(color) WHERE product_num IN (21,22,42…)\n<\/p>\n
\nFinally, you can have an XML "COLUMN SET" column for each table; this exposes the sparse properties (or perhaps a subset of them?) for each item as a collection of XML elements, for those folks that like to model these as XML.\n<\/p>\n
\nALTER TABLE products ADD COLUMN properties XML COLUMN_SET FOR ALL_SPARSE_COLUMNS\n<\/p>\n
\nIt's an interesting idea; the proof will be in the perf as well as the usability.<\/p>\n","protected":false},"excerpt":{"rendered":"
Many of you have already heard the "hardware store" story. What's the best way to model products in a hardware store, where new items arrive at the hardware store each day. Each item has a "short list" of similar properties (e.g. UPC, price) and a long list of dissimilar properties (e.g. paint has color, type, […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26,29],"tags":[],"class_list":["post-786","post","type-post","status-publish","format-standard","hentry","category-sparse-columns","category-sql-server-2008"],"yoast_head":"\n