SQL: Best practice to store various fields in one table

Question

Hi,

I want to design a table for items. There are many types of items, all share several fields. Each type of item has it's own fields. I want to store the uncommon fields in a separate table. I thought of something like :

----Items
+Item_id
+Item_Type_Id
+Item_Serial
...

----Item_types
+Item_Type_Id
+Item_Name
...

----Item_Fields
+Item_Field_Id
+Item_Type_Id
+Field_Name
...

----Field_Values
+Field_Value_Id
+Item_Field_Id
+Item_Id
+Value
...

The pro is having the ability to add fields and values without changing the tables. The con is that i have to transpose the field names and values in order to see all info for an item.

Any better suggestions? Or perhaps a simple (not stored procedure) way to join the tables to get a flat info? I tried to use PIVOT (I'm using SQL 2005) but with no luck.

Thanks.

Answer 1

One other option is to store items in XML format in one single field. Depending on your usage scenario, it may work well. Or it may not.

Answer 2

If you add these columns to the table, you can make them sparse columns to avoid the space taken by unspecified uncommon fields.

But I would not call this a best practice. (see comments under your question)

Answer 3

I believe there has to be some grouping of values.

For example lets say your items are objects in a room. Then different types of objects have different attributes. For example books have publication date and number of pages, chairs have color pattern and height, etc.

In this example, you make an item table, a book table and a chair table.

You could make an "additional values" table that holds generic information as above, but what you really want to do is figure out the "types" of the different groups of attributes and then make every one of those types it's own table.

Is there a set of values that all items have? There has to be at least one which is a type field (this describes where the other information is stored. I expect every item will also have a name and a description. This is the information to go in the item table.

Then you make additional tables for the different types itembook, itemchair etc. There may even be some overlap. For example itembook, itemhardback, itempaperback would be 3 tables used to describe books.

I believe this is the best solution to your problem. It will still allow you to extend, but it does put a framework around your data.

Of course there are systems that do it the way you describe, but unless you are building a tool that others are going to reuse for many different projects, it makes sense to design the system for the task at hand. You end up falling into the over designing trap otherwise. (IMHO)

On the other hand, if you are going to go the totally generic direction I suggest you use one of the systems that already exist that work in this way (entity framework, app framework, etc) Use someone else's don't start from scratch.

Answer 4

I don't want to be accused of being the always-uses-the-latest-useless-technology guy, but depending on your use case, this might be a good case for a nosql database - Tokyo, Mongo, SimpleDB, etc. Or as Developer Art suggested, you could just serialize the different fields into a single column. It's not the worst thing in the world.

Answer 5

I'm not too sure how you want to retrieve the info, but something like the below may work. (It's probably close to what Hogan mentioned.)

• If you want to retrieve data for a type, you can just JOIN two tables.
• If you want to retrieve data for all types (with all fields), you can LEFT JOIN all tables.

---

----Items
+Item_id
+Item_Type_Id
+Item_Common_Field1
+Item_Common_Field1
...

----Item_Type_A
+Item_id
+Item_Type_A_Specific_Field1
+Item_Type_A_Specific_Field2
...

----Item_Type_B
+Item_id
+Item_Type_B_Specific_Field1
...

Answer 6

I wrote a stored proc to make PIVOT more useful. Here is the source:

http://dot-dash-dot.com/files/pivot%5Fquery.sql

and some examples how to use it:

http://dot-dash-dot.com/files/pivot%5Fquery%5Fexamples.sql

For your data, the query would just be the raw data joining those tables above to produce a raw listing of:

set @myQuery = '
 Select Item_Id, Item_Name, Field_Name, Value From ...
';

Then your call to pivot_query would be:

exec pivot_query @myQuery, 'Item_Id, Item_Name', 'Field_Name', 'max(Value)'

like that.

Ron