I wanted to write a universal framework for web applications, but I ran into the problem of designing a database. The idea is as follows: There are categories, types, elements (nodes). Nodes can be of any type and belong to any category. A node can have a relationship with its children. For example: a node of the "city" type, includes nodes with the "street" and "river" types. I won’t figure out how to build a base for such a relationship.

  • and what database do you use? - Kostiantyn Okhotnyk
  • У ноды может быть связь с дочерними элементами. Well, a tree. Ноды могут быть любого типа и относится к любой категории. Well attributes. Everything is simple and flat ... where are the problems? - Akina
  • @KonstantinOkhotnick, MySQL - Maxim Vlasov

4 answers 4

The obvious solution is to use a Document-oriented DBMS . If the task requires the use of a Relational DBMS , then the logical solution, in my opinion, will be the implementation of a Document-oriented scheme in the context of a Relational DBMS.

To do this, you can use the following model:

Data storage model

Accordingly, each specific Node is a record in the Node table that defines only the name of the class that will be used to encapsulate data when loading from the database. The Value table, in turn, contains not only data of the “Property - Value” type, but also a link to the Node to which this data belongs, as well as the object identifier.

It remains to implement only the loader, which will correspond to the following semantics:

 interface ObjectRepository{ public Entity find(id, Entity); }

Implementing this is not difficult, but you need to remember that the solution is rather “hard”, since it lacks the advantages of a relational and document-oriented DBMS. Fortunately, the qualitative layer of abstraction will always allow to optimize the structure of such a database by transferring a part of the entities into separate tables.

    The Drupal is done in a similar way. Only there node is posts, but taxonomy is terms that can be structured parent-> child. Another separate table for key-> value settings

      Make two tables.

      In the first store all nodes:

       node_id|node_name 1 |City 2 |Street 3 |River 4 |House 5 |Fish

      In the second, denote the connection

       child_id|parent_id 2 |1 3 |1 4 |2 5 |3

        The question should initially not be in the base structure. you first, in theory, work out your objects, what properties they will have, etc. Then you will think how to create a data model. Otherwise, you get a nonsense and a flawed data model in the database. In other words, the structure of the base should reflect the interrelation of the models of the system objects within the framework of the worldview of the database.

        At the current level of your question you need 2 tables. In one list of nodes - such tables are called reference books. and the second is the connection between the nodes (the node code column, the column to whom it obeys). But believe the experience, with the current approach further problems will arise.