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

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

```sql
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?