Re: Property sheet, ad hoc, property page, flexible data

Marshall Spight wrote:
> David Cressey wrote:
> > "AC" <AC_at_No.spam> wrote in message news:TkFEe.4555$6M3.3607@trnddc03...
> >
> > PMFJI.
> >
> > The fundamental problem with user defined attributes is not a theoretical
> > one. It's one of conflicts between two incompatible requirements of the
> > users.
> > fine
>
> Strongly agree.
>
> Some more problems:
>
> Dawn describes two possible approaches.
> approach 1: add columns "UserDef1", "UserDef2"
> approach 2: additional table, with three columns: FK, Name, Value
>
> The first one is just frightening. It's not even in first normal form,
> and it will exhaust its expanision capacity quickly.

Yes, it is not a highly scalable approach, but does give more than the NOTHING that is often offered as an alternative to users. I'm a happier e-mail end users with gmail because it not only lets me tag an e-mail with multiple labels, but I can also click a star on e-mails as more information.

Users might be able to reuse the same single variable length character attribute for many purposes over the life of their system. These are basically "comment" attributes and you don't restrict the content of a comment. Name the comment "MultiPurposeShortComment" or "ProductNotation" (if an attribute of a product).

I'm guessing you have seen users request comments where a comment is not really what is needed, but they intend to put intelligent comments in the system so they can search on those. An attribute not named "comment" that is intended for this purpose would be better, right? Again, there are trade-offs and you need to assess the real requirements and design for flexibility for users as well as the system overall.

> The second one is better. Amusingly, I would describe that solution
> as being one that actually has well-defined semantics.

as would I :-)

> The semantics
> are that the first column specifies the entity being described, the
> second is the name of the quality being described, and the third is
> the value for that quality. Not a *lot* of semantics there, but at
> least it's well-defined. Note that I would not agree with Dawn as
> describing this as mixing metadata and data,

that is what I have heard it called. I would claim that we mix these a lot, so I do not have a problem with this. Metdata and data are all data.

> because the values in
> the name column *aren't* metadata, since we don't know what they
> mean, other than "they are names."

Who is the "we" in that sentence? The user knows what they mean and can, perhaps, write reports that qualify rows based on the value related to a particular tag. So, SQL doesn't grab this metadata from the same place, but there is still data about data stored in as attribute data, instead of in the system catalog.

> Another significant issue with these approaches is that they both
> do not have any way of allowing any structure in the user defined
> data;

Yes, that is both their charm and their downfall.

> you're limited to exactly whatever domains you allow up front;
> probably int and string, say, but certainly not, say, three pairs of
> int/float tuples and a list of x, y points.

Or perhaps a relation-valued attribute ...

You could toss in a few of these notational attributes as strings and perhaps a date or two. I'd prefer they were all defined as strings and cast to whatever is needed at the time, but in a SQL-DBMS, that might not be as easy.

My point is that you need to assess the needs and associated risks with any design you might choose and also consider the risk of ignoring this particular perceived need. I suspect that too many DBAs are inclined to tell their users they can't have such an attribute without doing an analysis and without realizing that there are real benefits to notational fields of this nature. I would suggest that even when a user does not identify this need up front, it is still a good idea to identify if there is a place for such attributes. I didn't ask for that gmail star, but I really appreciate it and suspect that it increases my productivity.

Cheers! --dawn Received on Sun Jul 24 2005 - 22:49:11 CEST