0

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.

Improve this question
6
  • do you have example data? i don't get the point. what data should have what relation? i.e. as example your nested set is a folder structure, where every node contains an image gallery. Commented Oct 8, 2015 at 11:16
  • 1
    A nested set is simply a tree in a database. There is no such thing as "a place to put actual data in". This means, either you put everything in the tree (e.g. by adding a value column) [bad idea imo], or you simply have dedicated tables holding your data. And these tables then use categories as an fk (or similar). Commented Oct 8, 2015 at 11:20
  • @Yoshi this is what I was thinking. So a table that has all the necessary fields - but surely these fields will be columns - so where does the category_id get stored against each field? Commented Oct 8, 2015 at 11:22
  • 2
    I'm not sure why you would want to map the tree against each field? I guess you need to explain more about your actual data-model. That said, I think you're making it way too complicated. An example: you use a nested set to store the channels of a forum. Using a different table you manage threads. A thread can have a multitude of attributes, but one will surely be the channel it belongs to. So use a foreign key in threads referencing the channels table. Commented Oct 8, 2015 at 11:26
  • 1
    Actually, no offense, this really is just basic database 101. So I don't think there will be many articles focusing on that. I guess you're simply confuesed because you think you need to stick everything into a tree structure. Commented Oct 8, 2015 at 11:36

1 Answer 1

Reset to default
1

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.)

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

but these fields will need to be columns, won't they? An example would be 'workplace' of type varchar with a character limit of 50 - this needs to be a column added to the table?
@CIvemy They will be columns in most cases. But if you don't need these columns by your queries, you can store data as JSON or TEXT too if you want. dev.mysql.com/doc/refman/5.7/en/json.html In that case it is better to use only a single table.
I think I am confusing myself because these bits of data need to represent form fields - which will obviously store data of a certain type. So I need to store the form fields, but also the data entered into them
@CIvemy I think this has nothing to do with nested set. If you already know which form fields you will use, then you should use columns and hardcode the forms to the app. If you don't know then you can use a single JSON or TEXT column and store all of the fields there and generate the forms with the app (not hardcode them). If you have different fields by each leaves, then you have to use JSON too. Probably in this case MySQL is not the best database, and you should use some noSQL db, for example mongodb.
I think you're right. I know the fields to begin with, but after that they can be added by a user. JSON sounds like a good idea actually, hadn't though of that. Why would mongodb (for example) be better in this case though?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.