Wednesday, February 13, 2008

table with infinite dynamically expandable columns veritcal rows propertes attributes eav otlt

If you need to have a table with attributes(columns) that need to be added dynamically, consider going vertically. There are various methods to add attributes to an entity by adding the attributes as rows.

you may be looking for these (Google them):

Entity-Attribute-Value model
“OTLT” (One True Lookup Table)
vertical model
vertical database
row modeling
attribute value system

Think deeply before you use any of these. Be careful you don't end up with a mess.
Dave’s guide to the EAV

OTLT and EAV: the two big design mistakes all beginners make

Entity-Attribute-Value (EAV) model with SQL Server 2005 xml datatype

An Introduction to Entity-Attribute-Value Design for Generic Clinical Study Data Management Systems

Entity-Attribute-Value model

Explanation of many database models including EAV


Generate or create XSD Schema from Database Table in sql server 2005

create table Person
Age int not NULL check( Age > 0) ,
Height numeric(10,2) not NULL check( Height > 5),
Gender varchar(5) not null check( Gender in ('M', 'F', 'O')),
BirthDate datetime null,
DECLARE @schema xml
SET @schema = (SELECT * FROM Person FOR XML AUTO, ELEMENTS, XMLSCHEMA('PersonSchema'))
select @schema