Re: Representation for Heterogeneous Attribute Set

 (robertbrown1971_at_yahoo.com) writes:
> 1. There are many types of bonds, each type has a different set of
> attributes, different attribute names, different attribute datatypes.

I don't envy you. Our customer's are mainly focused on stocks trading, and their volume of bonds are marginal. Nevertheless bonds are by far the most complex of our instrument groups. Your needs go far beyond ours, that's for sure.  

> 2. Typed Name-Value pairs.
>
> create table bonds (bond_id INTEGER, bond_type INTEGER, attribute_id
> INTEGER, int_val INTEGER, string_val VARCHAR(255), date_val DATE_
>
> Comment: The client does not like this because the table is sparse.
> Every row has two empty fields.

Louis had an example. Here is another one, where I use a constraint instead of a trigger (it's a real-world examples, with some of the columns deleted for brevity):

   CREATE TABLE systemparameters (

      sypcode       aba_sypcode          NOT NULL,
      ...
      typeofdata    aba_type             NOT NULL
         CONSTRAINT ckc_syp_typeofdata 
      CHECK (typeofdata IN ('B', 'D', 'F', 'I', 'V')),
      sypvalue      sql_variant          NULL
         CONSTRAINT ckc_syp_sypvalue 
       CHECK (sql_variant_property(sypvalue, 'MaxLength') <= 255), 
      ...
      CONSTRAINT pk_syp PRIMARY KEY (sypcode),
      CONSTRAINT ckt_syp_datatype_sypvalue CHECK
          (sql_variant_property(sypvalue, 'basetype') =
             CASE typeofdata
                WHEN 'B' THEN 'bit'
                WHEN 'D' THEN 'datetime'
                WHEN 'F' THEN 'float'
                WHEN 'I' THEN 'int'
                WHEN 'V' THEN 'varchar'
           END)

I understand from you broad cross-post that you may not be using MS SQL Server, or at least not only MS SQL Server. In such case sql_variant is not likely to save the day, since it's not portable. You could use a varchar column instead of sql_variant, but keep the typeofdata. You would then have to use a trigger to verify that that the varchar data conforms with the data, but this can be quite ugly, not the least for decimal values.

> Are the four approaches I described above exhaustive? Are there any
> other that I overlooked?
 

One big table with all attributes known today, and everything nullable except id and type? Of course, then you would not know which attributes that are mandatory for which types, unless you were to generate check constraints dynamically.

No, it does not sound very palatable. Either.

-- 
Erland Sommarskog, SQL Server MVP, esquel_at_sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp