CODEWITHSUNDEEP
phpmysqldatabase
DJC
DJC

Reputation: 1173

Structuring Data with Nested Set

I have decided to use the nested set model to structure one of my tables, which will contain categories, sub categories and sub-sub categories etc.

What I am struggling with is where / how to store the ACTUAL data, ie the end item. For example, parent category is 'Personal', sub-category is 'Goals' but the actual item/data could be a number (sent in from a select), or long text - obviously in reality it will only be one of these - but should this actual data be stored in a separate table?

What I have so far:

table 'categories'

+----+------------------+-----+-----+
| id |      title       | lft | rgt |
+----+------------------+-----+-----+
|  1 | persona          |   1 |  24 |
|  2 | demographics     |   2 |   7 |
|  3 | personal         |   3 |   4 |
|  4 | workplace        |   5 |   6 |
|  5 | technologies     |   8 |  15 |
|  6 | channels         |   9 |  10 |
|  7 | devices          |  11 |  12 |
|  8 | engagement       |  13 |  14 |
|  9 | goals_challenges |  16 |  21 |
| 10 | business         |  17 |  18 |
| 11 | career           |  19 |  20 |
| 12 | conversations    |  22 |  23 |
+----+------------------+-----+-----+

What I need is to be able to distinguish between these 'categories' and the actual data stored against them. For example I will have a form with a section 'Personal', which will have several form fields - like 'Marital Status', 'Number of Children' and this data needs to be saved, obviously.

For each client that is 'created', different data will be stored against these fields. My question is - where?

I get the nested set model and understand how it is working, but where am I to store the ACTUAL data?

Please let me know if you need more information, I have spent a lot of time on this and am not getting anywhere so any help would be much appreciated.

Thanks in advance.

Upvotes: 0

Views: 61

Answers (1)

inf3rno
inf3rno

Reputation: 26129

What you have here is an 1:1 relationship between the data and the nodes. You can model that relationship by adding the fields to the same table and setting null value by nodes which don't support these fields (e.g. by the branches in your case). Another option to have a separate table for the fields and use the pk of the tree as a foreign key if you want to add data to a node (e.g. to the leaves in your case). Both would work, it's your decision which one you choose. (I would say primarily opinion based question.)

Upvotes: 1

Related Questions