Hello, my name is Dmitry Karlovsky and I ... haven’t been engaged in a backend for a long time, but recently I stumbled upon SbWereWolf’s torture of putting a hedgehog on a hedgehog and couldn’t resist the temptation to blow the dust out of its OrientDB multi-tool and chop off something like that.

So, today we will be making a database for an online store with a search for products by parameters, full-text search, localization, automatic generation of a rubricator and a product adding wizard.

We will analyze this one here relational spaceship:

And collect here is such a graph birdhouse:

Database schema

All entities of our catalog will have the following fields:

• slug - human-readable identifier
• created - time to create an entity
• searchable - whether the entity will be in the search
• title - the human-readable name of the entity in the form of a language-text dictionary
• description - a human-readable description of an entity in the form of a language-text dictionary

In order not to repeat these fields in each entity, as is done in a starship, we simply create an abstract class "Object" from which we will further inherit other entities:

Create class Object abstract Create property Object.slug string ( collate ci , notnull true ) Create property Object.created datetime ( readonly true , default sysdate() ) Create property Object.searchable boolean ( default false ) Create property Object.title embeddedmap string ( collate ci ) Create property Object.description embeddedmap string ( collate ci ) 

A slug should be unique for each entity, and we will need a search by names and descriptions, so we add the appropriate indices to these fields:

 Create index Object.slug unique Create index Object.title on Object( title by value ) fulltext engine lucene metadata { "analyzer" : "org.apache.lucene.analysis.ru.RussianAnalyzer" } Create index Object.description on Object( description by value ) fulltext engine lucene metadata { "analyzer" : "org.apache.lucene.analysis.ru.RussianAnalyzer" } 

It’s nice that the full-text search engine is already built into the database and we don’t have to deal with manually transferring data from our main database to any ElasticSearch built on the same Lucene . When changing the essence of the DBMS itself will take care of updating the full-text index.

The most important thing in our store is the goods, and in addition to the standard properties, each product also has a price:

 Create class Product extends Object Create property Product.price decimal 

In a starship, a hierarchy of rubrics in the form of a tree is used, but we will proceed more interestingly - we divide the entire hierarchy into 2 types of nodes:

• Tag - a flag characterizing a particular product.
• Aspect - a group of mutually exclusive tags that form the characteristics of the goods

In the hierarchy, these two types of nodes go alternately:

• inside the tag may be aspects, but not tags
• tags can be inside an aspect, but not aspects

Examples of aspects (and in brackets - tags):

• Type of product (food, clothing, appliances)
• Manufacturer (Plyushkin Incorporated, McIntosh Limited, Excavator Trading)
• Color (Red, Blue, Green)

The choice of "color" makes sense to give only for the tags "clothing" and "appliances", but not "food".

Add these two entities and tie them together:

 Create class Aspect extends Object Create class Tag extends Object Create property Aspect.tag linkset Tag Create property Aspect.tag_sub linkset Tag Create property Tag.aspect linkset Aspect Create property Tag.aspect_sub linkset Aspect 

As you can see, all our relations are bilateral many-to-many. Each entity has a link to related entities. It remains only to link our hierarchy with the goods. The link will be one-way so as not to clutter up the tag with a list of related products, which we will not use anyway:

 Create property Product.tag linkset Tag 

For example, the tag “technique” may be set for the product “Tablecloth-self-dressing”, which will open the aspect of “color” and, as a result, the ability to choose “red”.

So that the search by tags does not slow us down, we will add an index to them:

 Create index Product.tag notunique 

In addition to flags, entities must also have attributes of other types: string, integer, decimal, temporal, and so on. To describe these attributes, we introduce the corresponding entity:

 Create class Attribute extends Object Create property Attribute.type string ( default "string" ) 

Attributes we will not be tied to the goods, as one might think, but to the tags:

 Create property Tag.attribute linkset Attribute Create property Attribute.tag linkset Tag 

For example, if the "food" tag is set, then the "shelf life" attribute becomes available for the product. The very value of the attribute of a particular product we will store in the product itself. We will not put this into the scheme, since each product may actually have its own set of attributes depending on which tags have been set for it.

Custom Scripts

Full text search

If the user entered a search query, we immediately search for all objects that correspond to him:

 Select from Object where searchable = true and ( title lucene "*" or description lucene "*" ) 

But if the issue turns out to be too large, it would be reasonable to offer him to detail the request for tags from the goods in the issue. To do this, we will request along with the actually found objects also associated tags and related attributes and aspects:

 Select from Object where searchable = true and ( title lucene "*" or description lucene "*" ) fetchplan *:0 slug:0 title:0 tag.slug:0 tag.title:0 tag.aspect.title:0 tag.attribute.title:0 tag.attribute.type:0 

Thus, next to the search results, we can place a specifying rubricator, the transition to the items which will narrow the issue, but is guaranteed not to lead to its complete cleaning. Therefore, we boldly add filtering by tags selected by the user and attribute values:

 Select from Object where searchable = true and ( title lucene "*" or description lucene "*" ) and ( tag in ( Select from Tag where slug = "tag=tech" and slug="color=red" ) ) and ( weight between 100 and 200 ) fetchplan *:0 slug:0 title:0 tag.slug:0 tag.title:0 tag.aspect.title:0 tag.attribute.title:0 tag.attribute.type:0 

Directory Navigation

When the user has just opened the site, it is reasonable to immediately offer him several directions of movement and delineate the range. Therefore, we derive all root aspects and possible tags for them:

 Select from Aspect where ( tag is null ) fetchplan *:0 title:0 tag_sub.slug:0 tag_sub.title:0 

The user's further journey is similar to the case of full-text search, but without actually full-text search:

 Select from Product where searchable = true and ( tag in ( Select from Tag where slug = "tag=tech" and slug="color=red" ) ) and ( weight between 100 and 200 ) fetchplan *:0 slug:0 title:0 tag.slug:0 tag.title:0 tag.aspect.title:0 tag.attribute.title:0 tag.attribute.type:0 

As you can see, plunging deeper into the rabbit hole, we do not move from one rubric to another (nested), but add an additional tag to the filtering. For example, on the "red shoes" page we will search by tags "clothing", "shoes", "shoes", "red", and on the "red notebooks" page - "equipment", "computers", "notebooks", " red. " In both cases, the "red" is the same tag.

Creation of goods

When creating a product, there is no point in listing all the possible parameters for the goods. For example, the parameter "signal / noise ratio" is completely meaningless for "shoes". Therefore, just as with the catalog, we display only the root aspects, and additional aspects become available only as tags are selected by the user who adds the product. The list of available attributes and aspects with their tags on the list of selected tags is rather trivial:

 Select from Aspect where ( tag is null ) or ( tag in ( Select from Tag where slug = "tag=tech" and slug="color=red" ) ) fetchplan *:0 title:0 tag_sub.slug:0 tag_sub.title:0 tag_sub.attribute.title:0 tag_sub.attribute.type:0 

Here we can provide the user with the ability to add not only the product, but also expand the set of attributes, aspects and tags.

Create, for example, the aspect "Type of product":

 Insert into Aspect set slug = "aspect=kind" , title = { "ru" : " " } 

Now add to it, for example, the tag "Clothes":

 Insert into Tag set slug = "tag=wear" , searchable = true , title = { "ru" : "" } , aspect = ( Select from Aspect where slug = "aspect=kind" ) Update Aspect add tag_sub = ( Select from tag where slug = "tag=wear" ) where slug = "aspect=kind" 

Other tags are added similarly. Adding an aspect to a tag is similar. For example, add the "Color" aspect to the "Clothing" and "Technique" tags:

 Insert into Aspect set slug = "aspect=color" , title = { "ru" : "" } , tag = ( Select from Tag where slug = "tag=wear" or slug = "tag=tech" ) Update Tag add aspect_sub = ( Select from Aspect where slug = "aspect=color" ) where slug = "tag=wear" Update Tag add aspect_sub = ( Select from Aspect where slug = "aspect=color" ) where slug = "tag=tech" 

And finally, the most important thing is the addition of goods. For an example, add a "self-painted cloth" in red:

 Insert into Product set slug = "product=2" , searchable = true , title = { "ru" : "-" } , price = 999 , tag = ( Select from Tag where slug = "tag=tech" or slug = "tag=red" ) 

Removal of goods

Deleting a product should not at all lead to deleting a record of this product from the database, so in the future you may need to restore this product or find information about it by its identifier from some log. Yes, even in order to issue 410 (Gone) instead of 404 (Not found), it is necessary that some product record still remains. In addition, there is such a complex problem as ensuring that no other entry refers to the one being deleted. Therefore, the best solution is to change the record so that it is excluded from certain processes. For example, to ensure that the product is not in the global search or in the catalog, it is enough to change the searchable flag to false . That is why in all search queries we specified the additional condition where searchable = true .

 Update Product set searchable = false where slug = "product=2" 

Another "delete" option is to delete references to the entity, instead of deleting the entity itself. For example, the list of aspect tags is stored in the tag_sub property. If we want to no longer choose the “Sulfur-ballon” tag in the “Color” aspect, then simply remove it from the tag_sub , but leave the link from the tag to the aspect intact. Thus, when viewing a product with this strange color, nothing will break - “Color: Sulfur Oil” will appear, but when creating a new product, it will be impossible to choose this color.

 Update Aspect remove tag_sub = ( Select from Tag where slug = "tag=gray-brown-magenta" ) where slug = "aspect=color" 

Summary

So we got 4 entities: product, tag, aspect and attribute. Between themselves, they have 8 types of links. And all this is enough to realize your Yandex.Market with search, filters and sorceresses in just one hectic evening.