opc.three (10/23/2012)
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
);
I have not tried nor studied this technique in any great detail, but have heard from people I trust that the technique does not scale particularly well.
Of course the ideal solution is to have all tables normalized, and I've never actually designed a table like this in production.
What's described above would be one form of a "property bag" technique. I have been tasked with building data extract and reporting from a large ISV database (PegaSystems) on Oracle that used this technique. From what I've been told, it's efficient from the application's perspective, because they can pull up a single patient's chart with only a few page reads. However, for aggregate reporting, the performance was bad, like full table scans. The developers actually had to replicate normalized versions of the tables just to facilitate the reporting queries.
In the later versions of both Oracle and SQL Server, it's possible to index XML columns, which would make it more scalable as far as querying, but I still prefer fully normalized table design. Fortunately I don't get many requests for tables that allow the users to create their own attributes or "fields" on the fly.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho