I need a data model that supports user-defined parent-child entities.
For instance, a super-user wants to create a "template" for a parent-child relationship, such as "Library -> Book", and then define the attributes for the Library entity, such as "Name", "Address" and "Head Librarian". Then they want to define the attributes for the Book entity, such as "Title", "Author" and "Subject". Then the normal-user wants to create instances of these, such as "George Washington Library" with a Book entitled "The Lord of the Rings"...
The kicker is that I, as the software designer, have no idea as to the number of entities, the number of attributes per entity or their type. This all must be able to be configured at runtime by the end-users.
I have tried a number of designs, but they all break one or more cherished normalization rules or have referential integrity problems.
Is their an established method or structure for such a user-definable model? Thanks in advance.
Am I correct in assuming that the users are interested in more than the
Library-Book relationship? That is, the super-user could create a
Manager-Employee or Country-Province relationship?
If that is correct, then there are two approaches I can think of. Neither is simple. In brief, they are:
Method 1: Code generation method. Method 2: Abstract Data Model
Method 1: Code generation
Create a GUI for the super-user so that he can define the parent-child relationship and the attributes for each entity.
Write a code generator that :
Creates the physical tables and the referential intergity relationship between them.
(Optional) Creates code that creates new packages for inserting data, updating data, etc.
The advantage of this method is that everything is explicit, all the data is in meaningful tables.
The disadvantages of this are that
a "rogue" super-user can create many tables;
it is not so easy to write code generators;
DBA may not be happy at ceding control to the users.
Method 2: Abstract Data Model.
This is simply a set of tables that define the following:
Obj_Type (name, ...)
Primary Key (Name)
For example, Library, Book, Manager, etc.
Obj_Hierarchy( parent_type, child_type, ...) Primary Key (parent_type, child_type)
For example (Library, Book, ...),
(Manager, Employee, ...), etc.
The table above defines the valid parent-child relationship.
The type_rows table just tells you what row is associated with what type.
For example, it may have a row with values type = 'Library', row_id = '1'.
Another row would be type = 'Book', row_id = '2'
A third row could be: type = 'Book', row_id = '3'
The type_values tells you the values of the attribute for that row.
In the above example, if Library has three attributes, then the type_values would have the following data:
I've looked at the data model you pointed to and I've got a question or two.
The table "table_records" has a primary key "rcd_id" and is linked to entity_attributes. How is a logical row in the entities table represented? It looks like the rcd_id is for an attribute of an entity. So how are the various attributes of a particular record identified?
Also, how can we relate a child record to its parent record?