How should I differentiate between system-defined and user-defined data?

Question

I'm using PostgreSQL and designing a relational database schema for an application where I want to store both system-defined (built-in) data and user-defined (custom) data in several tables, e.g.:

Product Categories

id	name	organisation_id
0	Vegetables	068fd6a5-1c93-493f-9ffc-93a52de02aa8
1	Dairy	068fd6a5-1c93-493f-9ffc-93a52de02aa8

The system-defined data is pre-populated and should be immutable by users, while the user-defined data can be created, modified, and deleted by users. I need to be able to query system and user data separately, and would like clients to remain dumb.

I've considered the following approaches:

1. Add a boolean column (e.g. is_system) to indicate whether the data is system-defined or user-defined, but given an incredibly low percentage of records will be system-defined, I'm not sure if this is a good idea.
2. Use separate tables for system-defined and user-defined data, even if the structure is almost identical. These feels wrong to me as the data is essentially the same.
3. Add a 'system-owned' organisation and identify system-defined data using its ID. Clients would need to be less dumb, because they now have an ID to remember if they want to query system data.

Which approach is generally recommended? Are there any other strategies I should consider?

Thanks for your insights.

Answer 1

Since your concern is one of security, i.e. restrict who can do what with different data, the simplest is your second option: keep your “system” data in separate tables, with different access controls. For example keep them in a separate schema = owned by the one user allowed to update this data, but readable by all other users. That way access controls are enforced automatically by your database. This mechanism will work with any relational database.

The first and third options imply that all queries and updates must include additional predicates to isolate system from other data. This makes the queries more complex to write and pushes the security requirements into the application code that accesses the data.

Some databases (like Oracle or PostgreSQL) have facilities that allow you to secure individual rows in a table, identified via a predicate (= based on column value like the “is_system” flag), called “row level security”. That may be an option if you use one of those databases.