One or two mysql tables for standard and custom categories?

Question

I fetch some standard categories from a MySQL table and present them in a select box for the user. On top of that I want to let the users add their own categories to the select box. If a user adds categories, only that user will see those categories. Everyone will see the standard categories.

Do you recommend to keep a separate table for all the custom categories and then fetch them from two tables and merge in the selectbox, or do you recommend to keep all the categories in one table and fetch the standard ones plus the ones with a user id set?

score 1 · Answer 1 · answered Sep 06 '17 at 17:45

In the performance scope, one table is obviously better.

In the design perspective, it is not that obvious: from one standpoint, custom-ness seems to be just an attribute to the entity; from another, it brings the relation to the user in, which may be considered as making up different entity.

That is you who knows the whole context (and so the cases in which these custom entities are supposed to be used/reused) better than anyone, so you decide what to go with.

Neels · Accepted Answer · 2017-09-06T19:55:43.700

0

As per my experience, keeping the categories in the same table would be advantageous for you in various situations. You can have a table structure like

|  Category Table      |
+----------------------+
| Category Details     |
| User ID              |
| Custom Flag          |
| Category Added Dates |

Having a custom flag that gets set every time a new custom category is added will let you classify your standard and custom categories easily. For the standard category, the user details can be set to Administrator or something similar. By following this approach, you can achieve the functionality that you are looking for in an optimized way.

edited Sep 06 '17 at 19:55

answered Sep 06 '17 at 17:26

Neels

2,547
6
33
40

Thanks! Is the custom flag better than just checking if user id is NULL or 0 or do you mean just if I need one more parameter I could use the custom flag in addition to the user id? – SeaBass Sep 06 '17 at 17:56
1

The Custom Flag is usually binary and has better performance in large tables when compared to checking for NULL user id. – Neels Sep 06 '17 at 19:44
So the field type would be BINARY and contain 0 or 1 for true or false? And that's better than tinyint(1) with 0 or 1 and the BOOL/BOOLEAN datatypes with I guess true/false in them? – SeaBass Sep 06 '17 at 19:47
1

From what I know, these datatypes are synonyms and can be used interchangeably without causing much difference in performance in large systems. Reference - https://stackoverflow.com/questions/3751853/boolean-vs-tinyint1-for-boolean-values-in-mysql – Neels Sep 06 '17 at 19:49

score 0 · Answer 3 · answered Sep 06 '17 at 17:38

Two tables approach looks better from productivity standpoint. But this is just for the first look. You can add column that specifies if category is custom and index it. Or even better, partitioning one table by condition:

CREATE TABLE categories (
    name VARCHAR(30),
    description VARCHAR(500),
    user_id INT
)
PARTITION BY RANGE( IFNULL(user_id) ) (
    PARTITION p_system VALUES LESS THAN(1),
    PARTITION p_custom VALUES LESS THAN MAXVALUE,
);

In this example user_id stores null for non-custom categories. Of course, you can make it better basing on your case, I just provide the main idea:)

This is interesting. What is the partitioning actually doing? Another type of indexing just separating the standard ones from custom for performance? — SeaBass, Sep 07 '17 at 16:10

One or two mysql tables for standard and custom categories?

3 Answers3