Best way to store muti-type field

Question

Let's say we have a field that can be in more than one type, for instance: string or date or XML datatypes.

Now we have two methods to store this in a database

1- using a string typed field + field defining type: losing "type-aware" sorting capabilities, needs casting

2- separate tables (StringValues,DateValues,Decimal,XML ...etc):a foreign key pointing to a value + field defining type : somehow complicated, performance

the second method may have an extra advantage if only unique values were stored: it will work as an index.

do you have something in mind ?

---

Note1: Preferably, consider project based on MS SQL Server 2008 and Linq2SQL

---

Note2: Maybe we will discuss how to implement EAV in another question, I'm asking about EAV in a relational storage.

---

Note3: Types can change, but not frequently

Answer 1

It sounds like you're designing an EAV solution, where your table stores values for multiple attributes, one value per row.

EAV is a non-relational design. There's no "right" way to do this with respect to proper rules of relational database design.

The proper design is to store each attribute in a separate column of one table. Give each column the right datatype and a descriptive name. Only store values of the same logical type in each column.

If you need dynamic attributes, use a non-relational data management solution.

Answer 2

I'm not sure this is enough detail to answer the question well. If you are literally asking about the two type case, you might also consider a table with a column for each type and a discriminator. The "right" answer may depend on specifics such as number of distinct types to be supported, speed vs. space constraints, etc.

Some might argue that the least expensive approach is the best one. Specifically, the approach that you believe will require the least cost to understand and maintain (often ~60% of TCO).

With regard to all the advice about not doing this, I agree if possible. On the other hand, SharePoint is one example that shows it's not impossible. Good luck!

Answer 3

I'd go with the second option and hide the complexity of the table situation with a couple of views. That way once you get more flexibility your applications can still point to the views without needing to be changed and you can rearrange your underlying tables to something a little cleaner.

Answer 4

Can you consider using an XML datatype? If so, you can use an attribute/element to define the type.

<string>My string value</string>
<date>24-Nov-1976</date>

Or,

<val type="System.String">My string value</val>
<val type="System.Date">24-Nov-1976</val>

SQL Server 2005+ has some good support for XML indexing that may support your needs.

From a Linq to SQL point of view you can probably have a lightweight class that can map the types to a specific data type; XML de/serialisation may be an option here.

Answer 5

If the number of possible types is small, use option 2 (additional tables + foreign key) or use option 3.

Option 3: Use one table with a field of each type and an enum field defining which field is relevant.

If the number of possible types is large or not constant, use option 1 (strings) - you can store dates in strings as YYYY-MM-DD-HH-MM-SS to preserve sorting.