"Flexible" Database - Suggestions

[Jeff Gaines]

I am working on a program which will produce meeting minutes and
membership lists for small organisations (I'm testing it live on an
organisation I am Secretary of).

One of the areas of data it will keep is details of members. Rather than
specify what data to keep for members I have written it so that the
members' DB has 20 text fields (in an Access DB) and there is a separate
record which provides a mapping from the Field name in the DB to what the
user plans to call it.

e.g.
Field1 = Last Name
Field2 = First Name
etc.

Is that a reasonable approach to providing flexibility?
Is there a name for this (apart from stupid) that I can Google for to get
some ideas?

It's working but since all fields are specified as text it lacks the
ability to sort by numbers etc.

 

[Jeff Johnson]

One of the areas of data it will keep is details of members. Rather than
specify what data to keep for members I have written it so that the
members' DB has 20 text fields (in an Access DB) and there is a separate
record which provides a mapping from the Field name in the DB to what the
user plans to call it.

e.g.
Field1 = Last Name
Field2 = First Name
etc.

Is that a reasonable approach to providing flexibility?
Is there a name for this (apart from stupid) that I can Google for to get
some ideas?

It's called a "name/value" or "attribute/value" approach. I have used it,
but only for "secondary data." I.e., if I know for a fact that I will be
storing information about a person, then there are certain "universal"
attributes that people have, like names, birth dates, etc. Those common
attributes are explicitly defined and named. Less common things I might dump
in a secondary name/value table.

 

[Jeff Gaines]

It's called a "name/value" or "attribute/value" approach. I have used it,
but only for "secondary data." I.e., if I know for a fact that I will be
storing information about a person, then there are certain "universal"
attributes that people have, like names, birth dates, etc. Those common
attributes are explicitly defined and named. Less common things I might
dump in a secondary name/value table.

Many thanks Peter & Jeff

I take the points Peter made, I think if I adopt Jeff's approach it will
be a good compromise between maximising the database's functions and
providing flexibility to the users.

 

[Arne Vajhøj]

I am working on a program which will produce meeting minutes and
membership lists for small organisations (I'm testing it live on an
organisation I am Secretary of).

One of the areas of data it will keep is details of members. Rather than
specify what data to keep for members I have written it so that the
members' DB has 20 text fields (in an Access DB) and there is a separate
record which provides a mapping from the Field name in the DB to what
the user plans to call it.

e.g.
Field1 = Last Name
Field2 = First Name
etc.

Is that a reasonable approach to providing flexibility?
Is there a name for this (apart from stupid) that I can Google for to
get some ideas?

It's working but since all fields are specified as text it lacks the
ability to sort by numbers etc.

I don't like the design.

Many reasons:
* all fields text of some length no matter what the content is => not
type safe
* SQL statements will be very difficult to read
* the limit of 20 seems arbitrary and you will most likely
not use all of them or run out of fields
* it does not really provide any flexibility in the database
and the flexibility in the application should not be implemented
in the database

If all members will have the same fields, then you should create
a table with the necessary fields. And if you don't want to always
change the app when table structure is changed then let the app
dynamically discover the table structure.

If all members will not have the same fields, then make separate
fields for those that will always be there and use one of the
following two approaches:
* a name value attribute table with 3 fields (member id, attribute name
and attribute value)
* an extra field where you store a XML snippet with the extra data

Arne