Mark as default - the optimal way

Hey guys,

An easy topic for today! Make something a default (e.g., category, product, campaign, etc).

Let’s go with the categories table for the examples. Let’s say we only want one category to be the default one. And find out which is the optimal way to deal with.

The easiest way

Usually, we would create a new column:

is_default: simplest column 0 for false and 1 for true
or a nullable defaulted_at (timestamp/datetime) column.

Upon marking a category as default, we’ll do two things:

Mark all categories as not default (UPDATE categories SET is_default = 0;)
Mark the selected category as the default category (UPDATE categories SET is_default = 1 WHERE id = xxx)

Upon fetching, we only need to include a simple condition (SELECT … WHERE is_default = 1)

Why it isn’t optimal?

We add a new column that 99% of the records are storing either NULL or 0 => unnecessary data stored.
We always have to update many records when marking a category as the default.

The optimal way

We create a new table default_categories (category_id int PRIMARY KEY).

Upon marking a category as default, we’ll do two things:

DELETE FROM default_categories // only one record
INSERT INTO default_categories(category_id) VALUES (xxx)

Upon fetching, a simple where exists condition (join is ok, too)

SELECT
...
FROM categories c
WHERE
    EXISTS (SELECT dc.id FROM default_categories dc WHERE dc.category_id = c.id)

With this, we don’t have to store unnecessary data, and we keep things super simple from both the database & application layers.

Going forward

Honestly, for applications nowadays, a user can be a part of multiple teams. So our default_categories might look like this:

default_categories
- id (int primary)
- team_id (FK to teams.id, UNIQUE)
- category_id (FK to categories.id)
(Note: foreign keys are optional; if you want extra integrity, add them)

Upon marking a category as default, it’s simpler, a single UPSERT (or updateOrCreate in Laravel):

INSERT INTO default_categories (team_id, category_id)
VALUES (:team_id, :category_id)
ON DUPLICATE KEY UPDATE category_id = :category_id;

Notes

This does not limit you to adding only one record as a default. Thinking default_categories of a pivot - many-to-many relationship table. Adjust it to match your needs.

We can have many default records, too!

Conclusion

Well, what do you think?

Mark as default - the optimal way