When creating master tables which are usually distinguished by a PK, say user_id, I'm often faced with the situation where I want to have a few pre-defined rows in the table.

I want data from these rows to apply to all users.

For example, I have a categories table, where I store categories (defined by the user) for products. However, I also want to have some predefined categories available to all users of the system - Shoes, Flowers, etc.

What is the appropriate way to handle this? I'm thinking:

id | user_id | description

and having standard ones defined so:

id  |  user_id  | description
1   |  null     | Shoes
2   |  null     | Flowers

Basically, the rows with null value in user_id indicate system defined categories. These should not be modifyable by users, but available for all.

I've read arguments against nullable columns in a table, so, I'm wondering what is the better way to handle this.

  • 2
    Use pseudouser like system or internal for entries not owned by real users. – Kondybas May 20 '18 at 7:15
  • Yes, - in the user table, give user no. 1 the name System. That way you can avoid the three-valued logic scenario and it's clear and simple. – Vérace May 20 '18 at 7:26
  • @Kondybas Please provide your comment as an answer and I will give it to you. – Code Poet May 20 '18 at 19:33

Sometimes it is reasonable to replace NULL values by placeholders. That allows to keep logic simple on the client side. There are two approaches here.

First is to use placeholders instead of NULLs directly in the tables. That make the data uniform but also means some loss of data because NULL have special meaning in the RDBMS.

Second approach is to replace NULLs by placeholders on the fly when data is passed to the client.

SELECT ifnull(user_id, 'system')
  FROM categories
. . . . .

That approach keep data untouched but require some modification of the SQL code.

There is no general rule, you have to decide what way is more suitable for your needs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.