Generic Data Model
15 Years ago I had a problem designing a database that was supposed to hold data from various questionnaires; the questions were slightly varying every time and had no
regular structure, reflecting the weird ways of thinking of marketing people. The solution was to produce something more or less generic, storing “entities” and “attributes” separately.
(there was some discussion of xml and corba, now totally irrelevant)
The model consists of three tables:
This is the main table in the database:
create table ENTITY (PK long, CLASS varchar, CREATED timestamp);
All the entities that we are going to have stored in our database have a representative in
ENTITYtable. An entity represents an object of our model. An object has an inalienable property, class. The term ‘type’ would probably suit better. An entity cannot change its class; actually, all the fields in entity record are read-only.
This table contains attributes of the entities. Records consist of name-value pairs and reference
create table ATTRIBUTE (ENTITY long, NAME varchar, VALUE varchar);
The usage of this table is probably obvious. Note that if NAME is null, it means that the attribute contains the value of the entity itself. Two other fields are non-null.
On one hand, this table can be considered as containing all the collections of the database; but on the other hand, the purpose of this table is wider. It contains all the naming contexts, and it can be used for navigation as well as for storing master-detail relationships.
create table CONTEXT (NAME varchar, OWNER long, MEMBER long, MEMBERID varchar);
NAMEis the name of the context. If you are interested in collections, it is probably the name of collection owned by
OWNERis obviously the entity that owns the context, the “master”.
MEMBERpoints to the member of the collection; in this case
MEMBERIDis irrelevant. But we can consider this as a Hash, in which case
MEMBERIDbecomes a key, and
MEMBERis the corresponding value. In the Naming Service paradigm,
MEMBERIDis the name of the entity
MEMBERin the context
These days the idea of key-value pairs has become ubiquitous; it was not so in 1996 or in 2001 when I wrote this. So I'm dropping the argument in favor of this, now obvious, solution.