Eric M Russell (10/23/2012)

---

Instead of separate denormalized columns, I'd rather contain user defined attributes in an XML column, preferably in a seperate extended type table. In the example below, each extended type (regular, contractor, temp, etc.) would have a seperate extype_id, and an employee could be of multiple types. Users can create their own types and dump their junk in it without cluttering up and fragmenting an otherwise normalized primary table. You can even create a view (or views) that join the two tables together, providing the users with a denormalized flat resultset that's easy for them to query and visualize like an Excel sheet.

create table employee

(

employee_id smallint not null primary key,

ssn char(12) not null,

hire_date datetime not null,

reportsto_id smallint null

);

create table employee_extype

(

primary key (employee_id, extype_id ),

employee_id smallint not null,

extype_id smallint not null,

exttype_xml XML null

);