Re: [OT] "advanced" database design (long)

vladimir konrad <vk@dsl.pipex.com> wrote:
>
> Hello,
>
> I think that I understand basic relational theory but then I had an
> idea. What I would like to know if this is sometimes done or that I am
> possibly mad... Also, I do not know the terminology for this kind of
> thing so I do not know where and what to look for.
>
> Basically, instead of adding field to a table every time there is a
> need for it, have a table split in two: one holds identity (id) and one
> holds the attributes (linked to this id). For example, modelling
> subject (person):
>
> subject:
>     id
>     entered_on (date when entered to the database)
>
> sadg (subject attribute group):
>     id
>     value (string - name for group of attributes, e.g. postal
> address)
>
> sad (subject attribute definition):
>     id
>     sadg (references sadg(id))
>     name (the name of the attribute - e.g. e-mail address, gender)
>     app_type (string - what data type to use in application for
> sa.value)
>     db_type (string - what cast to use in database for sa.value)
>
> sa (subject attribute):
>     id
>     subject (references subject(id))
>     sad    (references sad(id)
>     value    (string, holds the attribute value)
>
> sads (subject attribute definition set) - what values the sa can have:
>     id
>     sad (references sad(id))
>     value (string)
>
> Basically, if in the future user decides that the subject should have
> a new attribute, he can simply add "attribute definition" and
> attribute_definition_set (if any) and the application would handle it
> without re-designing table, queries, and screens (i.e. the user
> interface when displaying sadg: "postal address" would always pull all
> attributes in that group and lay-out would automatically handle the new
> attributes).

Sounds like LDAP.

--
Bill Moran
http://www.potentialtech.com