Schema advice for three level deep structure with persistent numbering

Data Service
1

I am looking for advice as to how to utilize Backendless the best way to create a good database schema for the following scenario:

I have an application where I want to create a three level deep datastructure, consisting of:

Main group

Has a unique number and a name only, e.g.

  1. Backend
  2. Serverless

Main Group has a 1:many relationship to its sub groups

Sub group

Has a unique number for within the main group it belongs to, e.g.

1.1. API
2.1. Code

Sub group has a 1:many relationship to its children

Children

Has a unique number for within the sub group it belongs to, e.g.

1.1.1. Naming convention
2.1.3. Headers

Hope this makes sense.

What I am wondering about is not so much the relationships, but rather how to implement the numbering here in a good way. How can I get the numbers to be so that I can easily retrieve the numbers for children objects, and still get their parent (sub group and group) numbers included, while still maintaining the possibility of reordering the groups and subgroups in a good way?

Is the best solution to have a number field for each of the levels in the schema, and just use a view to combine these, or does that tax the database a lot?

Thanks for any advice on this.

2

Hi Egil,

This is a great question. Do you mind sharing the significance of the numbering scheme in the app?

Is the numbering important only to the users in the UI or be also present at the database level?

Regards,
Mark

3

I’ll be happy to! My first thought waking up today was actually that I did a poor job explaining my question… :slight_smile:

What I am designing is an app where a document is created at the end, where the numbering scheme is supposed to be used to identify each of the items, as well as be their sort order internally. Think of it as a legal document, where you have a setup like:

  1. Topic A
    1.1. Subtopic A
    1.1.1. Paragraph A
    1.1.2. Paragraph B
    1.1.3. Paragraph C
    1.2. Subtopic B
    1.2.1. Paragraph D
    1.2.2. Paragraph E
    1.2.3. Paragraph F
  2. Topic B
    2.1. Subtopic C
    2.1.1. Paragraph G
    2.1.2. Paragraph H
    2.1.3. Paragraph I

When this document is generated, the numbering refers to the actual topic and paragraphs so that it is the main key to communicating around what this is all about. The same goes during the development process of this document, which the app is supposed to handle, but then there is the possibility of sorting and altering the list of topics, subtopics and paragraphs as part of the process. For instance, after a work session, the above list might be reshuffled to look like (and have numbering like) this:

  1. Topic B
    1.1. Subtopic C
    1.1.1. Paragraph H
    1.1.2. Paragraph I
    1.1.3. Paragraph G
  2. Topic A
    2.1. Subtopic B
    2.1.1. Paragraph D
    2.1.2. Paragraph E
    2.1.3. Paragraph F
    2.2. Subtopic A
    2.2.1. Paragraph A
    2.2.2. Paragraph B
    2.2.3. Paragraph C

So, any reordering of the numbering will possibly happen before the document is locked, but the topics that have an internal relationship should keep that when you for instance re-number/sort Topic B (all subtopics and paragraphs should keep their internal numbering/sort-order).

All that said, there is no real reason to have that numbering present at the database level as such, other than that it will need to be locked when the document is complete, and also easily retrieveable for just a single paragraph (e.g. 2.1.3.) when you want to do for instance a search across all paragraphs to find the ones containing the phrase “to be deleted”…

I have thought about having a separate number field used to handle sort order, that is not visible, but I still need to have a visual reference to the actual number the item at hand has.

Hope that was clarifying, and that you have some good tips and advice here :slight_smile:

4

Hi @Egil_Helland ,

Sorry for the long delay with response.
Considering all information which you have provided I can suggest you to store number inside of a group in “order” columns of each of the tables (“Main group”, “Sub group”, “Document”). With this approach full number will be assembled on the client side. There is no other way here since you want to be able to rearrange documents.

If to compare performance of retrieval between tables and view I would recommend to use direct fetch from the tables since views represent data collected to specified form “on the fly” and queries to view can take more time. But at the same time with data from the view it can be easier to work on the client side.

Regards, Andriy