Best way to store muti-type field

Question

Say we have a field that can be of more than one type, for example: string or date or XML data types.

Now we have two ways to store this in the database.

1- using a string typed field + a field defining a type: losing sorting by type, listing is required

2- separate tables (StringValues, DateValues, Decimal, XML ... etc.): a foreign key indicating the type of value + field: somehow complex, performance

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

Do you have something in mind?

---

Note1: It is recommended to consider a project based on MS SQL Server 2008 and Linq2SQL

---

Note2: Perhaps we will discuss how to implement EAV in another question, I ask about EAV in relational storage.

---

Note3: Types may change, but not often

Answer 1

I am not sure that this is enough to answer the question well. If you are literally asking about two types of cases, you can also consider a table with a column for each type and discriminator. The “correct” answer may depend on specific features, such as the number of type types supported, speed and space limitations, etc.

, . , , , , ( ~ 60% ).

, , , . , SharePoint - , , . !

Answer 2

, EAV, , .

EAV - . "" .

- . . .

, .

Answer 3

. , , , - .

Answer 4

XML- ? , / .

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

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

SQL Server 2005+ XML, .

Linq to SQL , , , ; XML de/serialization.

Answer 5

, 2 ( + ) 3.

3: , , .

, 1 () - YYYY-MM-DD-HH-MM-SS .