Re: Representation for Heterogeneous Attribute Set

Here's some sample MSSQL code for the Variant approach.

CREATE TABLE bonds (bond_id INTEGER, bond_type INTEGER, attribute_id INTEGER, data_type BIT, value SQL_VARIANT) GO
CREATE TRIGGER bonds_value_validate
ON dbo.bonds
FOR INSERT, UPDATE
AS
BEGIN
  IF EXISTS(
  SELECT *
  FROM INSERTed
  WHERE SQL_VARIANT_PROPERTY(value,'BaseType') <>     CASE data_type WHEN null then 'datetime' WHEN 0 THEN 'varchar' WHEN 1 THEN 'int' END
  ) BEGIN
  ROLLBACK
  RAISERROR ('Bad Value', 16, 10)
  END
END
GO
INSERT bonds VALUES (1,1,1,null,cast('20050101' as datetime)) GO
INSERT bonds VALUES (1,1,2,0,'abcdef')
GO
INSERT bonds VALUES (1,1,3,1,12345678)
GO
-- this one will throw an error
INSERT bonds VALUES (1,1,3,0,12345678)
GO

SELECT data_type,cast(value as
char(11)),cast(SQL_VARIANT_PROPERTY(value,'BaseType') as char(10)) FROM bonds

/* output:

NULL      Jan  1 2005 datetime
0         abcdef      varchar   
1         12345678    int