Re: User-defined fields

From: Daniel A. Morgan <damorgan_at_exesolutions.com>
Date: Mon, 07 Jan 2002 08:36:20 +0000
Message-ID: <3C395E04.2997DD9C@exesolutions.com>

This end-user request, in my experience, always ends up in a disaster. My response, when logic fails to convince them, is as follows:

Think vertically ... not horizontally.

So rather than ending up with a design like this:

CREATE TABLE xyz (

  field1   VARCHAR2(200),
  field2   VARCHAR2(200),
  field3   VARCHAR2(200),

  ...etc...

you get

CREATE TABLE xyz (

   pk_field NUMBER,
   fieldtype NUMBER,
   textvalues VARCHAR2(200))

Use the field type to designate which values would correspond with the above field1, field2, or field3.

The advantage is that an unlimited number of fields can be represented though this does not handle FKs.

If they want FKs then you end up writing a very involved package using NDS and performance, invariably degrades over time.

Daniel A. Morgan

Carl Willis-Ford wrote:

> I work primarily with Oracle, but this question is relevant to just
> about any RDBMS, I would think.
>
> I have a customer wanting an 'extremely flexible, scalable' database
> design. Of course, they already have the solution...design a standard
> relational database, but then allow for users to add their own fields
> as they need new things to be stored in the database.
>
> The customer doesn't have details about how the RDBMS will work with
> these fields, just saying that the 'database experts' can figure it
> out.
>
> Essentially, they talk about tables describing relationships, with the
> records in the tables holding 'metadata' about the data as well as the
> data itself. (not a good description, I know, but that's what I have
> right now...)
>
> My concerns are centered on things like how to handle FK's, indexing,
> ad-hoc query tools, how the optimizer will work, etc.
>
> I see out on the web that there are many COTS type products that use
> databases and advertise customized or user-defined fields, but I
> haven't seen any white papers or other treatises on this idea.
>
> Wondering if anyone could share some insight here.
>
> thanks!
Received on Mon Jan 07 2002 - 02:36:20 CST