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.

This looks like an interesting problem.


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 :

1. Creates the physical tables and the referential intergity relationship between them.
2. (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.

Type_Attributes(Attribute_Id, Type, attr_name, attr_data_type, ...)

Primary Key (Attribute_id)
Unique Key (type, attr_name)

This table defines the attributes of the various types.
For example: (1, Library, Name, Char, ..),
(2, Library, Address, Char, ..), etc.

Additionally, you would need a table to store the values of the various rows of the various types. It could take the form
Type_Values (Type, attribute_id, row_id, attribute_value, ...)

Primary Key (type, attribute_id, row_id)

The column "row_id" is just some sort of row identifier, not the "ROWID" of Oracle database.

And yet another table to show what child row links to parent row.
Obj_Hier_Type_Values (Parent_Type, parent_row_id, child_type, child_row_id, ...)

If row_ids are unique across the database, then you do not need the parent_type and child_type columns in the above table.

This table can be avoided if a "parent_id" attribute is defined for each child table.

As you can see, this becomes very abstract and it is difficult to understand or query this.
At the very least you would have to create many views to make sense of the data.



My preference is for the code generation method.

Hope I understood your problem correctly.


Ravi

Ravi,

You are correct. The database must hold "library - book" as well as "manager - employee" and "country - province".

My work so far has been to construct an abstract data model. Doing this with complete referential integrity is the difficulty.

Regarding the referential integrity, the suggested data model can easily handle it.

Look at the tables:

Obj_Hier_Type_Values (Parent_Type, parent_row_id, child_type, child_row_id, ...)

and

Obj_Hierarchy( parent_type, child_type, ...)


If we have a foreign key from obj_hier_type_values to obj_hierarchy table, as follows:

FK_hier (parent_type, child_type) references obj_hierarchy(parent_type, child_type)

This ensures that you can not have a Library-Employee relationship.

On the question of ensuring that the data is correct, we can restructure the type_values table into two:

(OLD)
Type_Values (Type, attribute_id, row_id, attribute_value, ...)

(NEW)
Type_rows (type, row_id)
AND
Type_Values (Type, row_id, attribute_id, attribute_value, ...)

Now, have two foreign keys from the obj_hier_type_values table to the type rows table as:

FK_parent (parent_type, parent_row_id) references type_rows(type, row_id)
and
FK_child (child_type, child_row_id) references type_rows(type, row_id)

This takes care of the data integrity issues.

Hope that helps.

Ravi

Ravi,

Thank you for your help so far. My problem now is visualizing the relationships you have outlined. So I'm using MS SQL Server and Enterprise Manager to create your structure.

I'm getting lost somewhere between ObjHierarchyTypeValues, TypeRows and TypeAttributes. I'll keep working at this and post back here when I get somewhere!

I hope the following explanation helps.


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:



Row1: Type = 'Library', row_id = 1, attr_id = 'Name', attr_value = 'Public Library'
Row2: Type = 'Library', row_id = 1, attr_id = 'Address',
attr_value = '1 Main Street'
Row3: Type = 'Library', row_id = 1, attr_id = 'Librarian',
attr_value = 'John Dewey'

A book could be represented as:

Row4: Type = 'Book', row_id = 2, attr_id = 'Title', attr_value = 'The Blind Watchmaker'
Row5: Type = 'Book', row_id = 2, attr_id = 'Author', attr_value = Richard Dawkins'
Row6: Type = 'Book', row_id = 2, attr_id = 'Publisher', attr_value = 'ABC'

and so on.

And the obj_hier_type_values would link the row_id 2 (book) to the library with row_id 1. The row would be:

('Library', '1', 'Book', '2')

This would be read as: The library identified by row_id 1 has the Book identified by row_id 2.

We are assuming that the combnation (Library, Book) has been entered in the obj_hierarchy table.

Hope that did not confuse the issue more.

Ravi

You might be interested in the Data Model for User-Defined Hierarchies on this page of my Database Answers web site :-
http://www.databaseanswers.com/data_...hies/index.htm

Barry Williams
Principal Consultant
Database Answers

Hi Barry,

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?

COuld you please explain these to me?

Thanks,
Ravi

Thanks to Barry and Ravi for all your advice. I am slowly processing all of your suggestions...

Now on Barry's model, relating child records to the parent happens via the self-join on the Entities table via the entity_id and parent_entity_id fields, yes?