Need recommendation for a table structure

Question

I have an entity which has 4 different types of property that could have only one value for each case which are boolean, decimal, string or text. I don't want to define the table with 4 boolean, decimal, nvarchar and ntext columns. What would you recommend to cover this case?

Update:

I'm using MS SQL Server.

Here is the class definition:

public class Foo
{
    public int Id { get; set; }
    public string Description { get; set; }
    public bool BooleanValue { get; set; }
    public decimal DecimalValue { get; set; }
    public string NVarcharValue { get; set; }
    public string NTextValue { get; set; }
} 

Answer 1

I'll presume that you want to use MS SQL Server.

If this is the case then I wouldn't use ntext This datatype is due to removed in future versions of SQL Server. If you really need to use a non-Unicode datatype then use nvarchar. Another restriction of using ntext is that you can't convert it to a lot of the other datatypes. Of the 30 odd datatypes, you can only convert ntext to six of them.

What you could do in this scenario is have the following table structure.

Create Table dbo.PropertyInformation
(
PropertyId int identity(1,1) not null,
PropertyName varchar(50) not null,
PropertyTypeId int not null,
PropertyValue nvarchar(max)
)

Then add a lookup table to store your property types

Create table dbo.PropertyTypes
(
PropertyTypeId int identity(1,1) not null,
PropertyType varchar(50) not null
)

You can store any type of value in the PropertyValue column as nvarchar converts to almost all other datatypes - apart from image. Read the PropertyType from the lookup table and convert the value of PropertyValue on the fly within your App.

Although, do you have a reason why you don't want to have 4 different columnns to store the property values?

ntext datatype information

Casting & Converting in SQL Server (The matrix of allowed conversions is about half way down the page)

Answer 2

You could define 4 tables for each of the properties and each row of each table would have a foreign key to the entity that it belongs to.