Model document-oriented database in a relational database
Type of tool: data management system (CMS) for web applications.
Implementation: PHP, MySQL.
')
From the end user's point of view, all possible technical implementation options seem complicated or simple GUI, in which he can contribute one or another set of data. Whatever the GUI would be, it is limited to the applicable data input elements (controls): input, select, file, text etc. This data set describes a document (record) and is a set of its properties. Based on the valid control options, the properties can either have a numeric id value of another element (reference book), or can be described with text.
Since the implementation of a document-oriented database is considered from the point of view of the CMS, there are several additional requirements: a part of the data should have quick and easy access to perform a quick and simple search and perform the most frequent sorts, besides, there should be a mechanism for simple control of document properties and data entry.
With theorizing over, let's go.
The first and main table is the storage of the objects themselves (documents, records).
The storage of documents in the database is the simplest - each new document is assigned an id, an id of the parent element, a text field for storing properties and a number of additional fields about which later.
id Property name parent add_field
1 Record_1 xml_properties 0 add_field
2 Record_2 xml_special 0 add_field
3 Record_3 xml_vary 2 dop_polya
4 Record_4 xml_vary 2 add_field
Properties can be stored in any way: from banal separators; and | to json and xml.
I chose xml because of the simple mechanisms for working with it in PHP and the implementation of xPath in MySQL
We assume that each document is described by a set of properties specified in a certain template - the Content Template (SH).
The following table is a table describing the property sets for documents (CS). The table has a textual description of the template, and an arbitrary number of tabs subordinate to it, which in turn can be given an arbitrary number of fields.
CC name
Tab_1
field_1 Text
field_2 List
field_3 Date
Each entry in the main table binds which CS with the data to it.
Naturally, in addition to the data, some representation of this data is necessary for the user on the site (do not forget that we are CMS).
The following table is a table describing the Design Template (LH) for presenting data from Template Content. LH can be built on the basis of any template engine. We use a primitive mixture of PHP and HTML.
Now let's recall the limitations that we have imposed and return to the additional fields.
Well, first of all, id related to the KSh and LH entries fall into the additional fields. In addition, in CS, you can specify part of the fields for storage not in xml, but directly in the database table (such as link1, link2, link3). There are several pieces of these fields for valid data types in MySQL - text, numbers, dates.
Additional fields include keywords and descriptions of documents, as well as information about the access rights of various users.
What are the advantages of this approach:
A simple and flexible template customization mechanism for entering data for an unqualified operator.
getting the record by id in one request from the database gets the maximum number of its properties.
the ability to use the xPath mechanism for working with XML in MySQL.
What are the disadvantages:
impossibility of native sorting according to data lying in XML when stored in MySQL
slow sampling for LIKE '%' design
The principle is described, although there is a fully implemented CMS on which I am happy to work with quite large projects.,
Source: https://habr.com/ru/post/103759/
All Articles