What is the best way to model custom hierarchical relationships in a database?

Question

Essentially, I want the user to be able to define a hierarchical model, but then I need to allow the user to store data within a specific model. Does this make sense? Thus, users will be able to create new "unit types" that will be organized in a hierarchical manner and decide how to organize units of these types. A simple example: in my hypothetical interface, the user creates three types of units, a trunk, a branch, and a leaf. The user then determines the relationship between them. A leaf can exist at any point in the hierarchy; a branch must have a chest as a parent. Then the user can create instances of these unit types (as units) and organize them according to the rules defined in their model ... is there a good way to do this in the database?

Answer 1

This is a very broad question, but it may point you in the right direction. Please note that you can store relationship rules in a database. Their use will match your customer code. Try this for size ..

unit:
    unit id,
    name,

unit relationship:
    unit id,
    foreign unit id

Then you can use the relationship table with your device as follows.

unit idrefers to the unit being described. foreign unit idmust be zero.

A unit . A unit null foreign unit id unit . a unit unit, , .

, .

instance:
    instance id,
    unit id,
    parent instance_id

, (, ), , .

Answer 2

:

• " "

, .

create table unittype
(
    id int;
    name varchar(20);
)

create table unitrelationship
(
    id int;
    parent_id int;
)

:

create table hierarchy
(
    id int;
    parent_id int;
    unit_type_id int;
    unit_id int;
)

, .

create table unit
{
    id int;
    ....
}

, , , , .

Answer 3

, ( , ). " SQL Smarties" (ISBN: 1558609202). , , .