I have the following case. I am using a nested set model to store hierarchical data. The model is pretty much the same apart from the fact that I also want to add the option of having a specific subcategory to be available only to a certain client(s). Editing the table would be done externally from a VB.NET app which would then send the new table back to the database.
I have thought of two different options to accomplish that. The first is to add a new client_id field which concatenates the client ids. Then I would perform a query to look for the specific number in the string. The #1 refers to all users whereas, a number different from that refers to a specific client.
+-------------+----------------------+-----+-----+---------+
| category_id | name | lft | rgt |client_id|
+-------------+----------------------+-----+-----+----------
| 1 | MAINCAT1 | 1 | 20 | 1|
| 2 | subcat1 | 2 | 9 | 2;4|
| 3 | subcat2 | 3 | 4 | 3|
| 4 | subcat3 | 5 | 6 | 1|
| 5 | subcat4 | 7 | 8 | 2;3|
| 6 | MAINCAT2 | 10 | 19 | 1|
| 7 | subcat1 | 11 | 14 | 1|
| 8 | subcat2 | 12 | 13 | 1|
| 9 | subcat3 | 15 | 16 | 3;4|
| 10 | subcat4 | 17 | 18 | 3;4|
+-------------+----------------------+-----+-----+---------+
The second option is to add a new field for every client and use some sort of a mark e.g 1/0 to enable/disable the categories.
+-------------+----------------------+-----+-----+---------+---------+
| category_id | name | lft | rgt |client_2 |client_3
+-------------+----------------------+-----+-----+---------+---------+
| 1 | MAINCAT1 | 1 | 20 | 1| 1|
| 2 | subcat1 | 2 | 9 | 0| 1|
| 3 | subcat2 | 3 | 4 | 0| 1|
| 4 | subcat3 | 5 | 6 | 1| 0|
| 5 | subcat4 | 7 | 8 | 1| 0|
| 6 | MAINCAT2 | 10 | 19 | 1| 1|
| 7 | subcat1 | 11 | 14 | 0| 1|
| 8 | subcat2 | 12 | 13 | 0| 1|
| 9 | subcat3 | 15 | 16 | 1| 0|
| 10 | subcat4 | 17 | 18 | 0| 1|
+-------------+----------------------+-----+-----+---------+---------+
I would prefer going for the first option as it doesn't require adding/removing fields. I suspect performing queries would be slower, however I don't plan to make the table very lengthy. Which method do you think is more adequate?
The first option means finding the matching id in a potentially very large string (and ultimately limiting the number of clients who can see that category). If you must do this (and I would advise against it) then make the list comma separated as you can then at least use the FIND_IN_SET() function.
The 2nd option seems to require a column per client, which will be a nightmare as you get more clients.
I would suggest you have another table which has multiple rows per category, one for each client that can see that category. Using a simple join you can check that a category can be seen by the relevant client.
EDIT - 2 table solution would mean adding a table linking categories to clients. Simple table like this:-
CREATE TABLE category_client
(
category_id INT(11),
client_id INT(11),
PRIMARY KEY (`category_id`, `client_id`),
KEY `client_id` (`client_id`)
);
INSERT INTO TABLE category_client (client_id, category_id) VALUES
(1, 1,
(2, 2),
(2, 4),
(3, 3),
(4, 1),
(5, 2),
(5, 3),
(6, 1),
(7, 1),
(8, 1),
(9, 3),
(9, 4),
(10, 3),
(10, 4);