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).
The advantage I see, is that to add new fields (here "attributes") no
db development would be needed and user could do this.
The disadvantages I see is that the model is hard to work with (i.e. how
do I see subject (with attributes) as a table - could cross join be
used for this?. Also, hand writing the queries for this would be hard
(possibly needed if user would like to write custom reports).
Do people do this kind of thing (or I took it too far)? If yes, I would
be grateful for pointers to examples or any other info on this...
Vlad