Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    4

    User-definable data model...

    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.

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

  3. #3
    Join Date
    May 2004
    Posts
    4
    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.

  4. #4
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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



  5. #5
    Join Date
    May 2004
    Posts
    4

    Smile

    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!

  6. #6
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

  7. #7
    Join Date
    Apr 2003
    Location
    London, England
    Posts
    42
    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

  8. #8
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Barry's Generalized Hierarchy Data Model

    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

  9. #9
    Join Date
    May 2004
    Posts
    4

    Still processing all this...

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •