CODEWITHSUNDEEP
mysqldatabase
SeaBass
SeaBass

Reputation: 1764

One or two mysql tables for standard and custom categories?

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?

Upvotes: 1

Views: 86

Answers (3)

Neels
Neels

Reputation: 2543

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.

Upvotes: 0

Yuri G
Yuri G

Reputation: 1213

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.

Upvotes: 1

Oleh Rybalchenko
Oleh Rybalchenko

Reputation: 8059

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

Upvotes: 0

Related Questions