Is it wrong to allow user initiated DDL commands to facilitate an extensible schema

[Karen Sundquist]

I have a client who wants to build a database with a user extensible
schema.

The client wants to model an entity with about 100 numeric attributes.
As time goes on, more numeric attributes will be added. The client
wants an admin function that will allow him to add these attributes
when needed.

My instinct is that this is wrong and that bad things will happen if
we build this. However, when I went to write up the reasons, all I
could come up with was that
* we would need to write dynamic SQL to handle the changing table
structure,
* that table locking would result from field additions
* and that it was "non-standard"

One alternative is to create a table containing field-name/value
pairs. This is ugly and breaks the ER modelling rules that tell me yo
put attributes of an item into the same row. All the same, I would
prefer this method to the first approach.

This problem does not arise on database projects that follow the
pattern of only allowing DDL commands to be issued during the initial
development phase and during subsequent change requests.

Have any of you been faced with a similar problem? What should I do?

Karen

[Erland Sommarskog]

Karen Sundquist (ka********@yahoo.com) writes:

One alternative is to create a table containing field-name/value
pairs. This is ugly and breaks the ER modelling rules that tell me yo
put attributes of an item into the same row. All the same, I would
prefer this method to the first approach.

This would be my preference too.

Of course, neither this is going to be fun if you want reports with
these attributes as columns. You could define views that twisted
the rows as columns, but with over 100 columns that will be a horrible
self-join if all attributes are to be included.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Mischa Sandberg]

Actually, the grotesque-looking aggregation of the attribute/value table
is reasonably fast.
It also gives you a way to enforce TYPES on the values,
which is another real issue with attribute/value format.
The underlying table is still amazingly spacewasteful,
but if you HAVE to ....

CREATE VIEW Normal AS
SELECT key1, key2, ...
min(case when attribute = 'name' then value end) as name,
min(case when attribute = 'asof' then cast(value as
datetime) end) as asof,
...
FROM Abnormal
GROUP BY key1, key2, ...

I can't speak for the MSSQL engine, but proper dragging and beating
in the query tree ought to eliminate any processing to support
unused columns (MIN(...) expressions) when making reference
to the view.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Karen Sundquist (ka********@yahoo.com) writes:

One alternative is to create a table containing field-name/value
pairs. This is ugly and breaks the ER modelling rules that tell me yo
put attributes of an item into the same row. All the same, I would
prefer this method to the first approach.

This would be my preference too.

Of course, neither this is going to be fun if you want reports with
these attributes as columns. You could define views that twisted
the rows as columns, but with over 100 columns that will be a horrible
self-join if all attributes are to be included.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Karen Sundquist]

Mischa and Erland, thanks for your advice. I am going to go with the
field/value pairs, as I did the last time I had this problem.
Karen

[Erland Sommarskog]

Mischa Sandberg (mi*************@telus.net) writes:

It also gives you a way to enforce TYPES on the values,
which is another real issue with attribute/value format.

Apparently in Karen's case, the attributes were numeric, so maybe it's
issue this time.

In the general case where attributes are of mixed types, you can use the
the sql_variant datatype, and supplement with a column which defines
the datatype. Then you have a constraint which goes like this:

sql_variant_property(value, 'Basetype') =
CASE typeofdata
WHEN 'B' THEN bit
WHEN 'D' THEN datetime
...
END

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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