Re: Representation for Heterogeneous Attribute Set

> 1. Name-Value pairs.
>
> create table bonds (bond_id INTEGER, bond_type INTEGER, attribute_id
> INTEGER, value VARCHAR(255))
>
> Comment: The client does not like this approach because they want to
> run various kinds of reports and thus they doe not want the values to
> be stored as VARCHAR. They want the DB to enforce the datatype.

One possible approach is to use a Variant datatype. In MSSQL, the "sql_variant" datatype can hold any non-BLOBs. Then add a DATA_TYPE column where: null is date, 0 is varchar, and 1 is integer. Use a trigger to ensure that the VALUE can be explicitly casted to the specified data type.

CREATE TABLE bonds (bond_id INTEGER, bond_type INTEGER, attribute_id INTEGER, data_type BIT, value SQL_VARIANT) Received on Fri Feb 11 2005 - 15:36:36 CST