Common database table design patterns
This article is yet to be finished.
Content
These tables are for storing pure content. A really good example would be blog articles. This data is often formatted and showed to the end users over a read-only channel. There is usually a moderator or an editor who can change this data.
It often has full CRUD (create-read-update-delete) access though certain actions are restricted to certain roles. For example, a blog editor can create, update or even delete an article but the end user can only read it.
This data can be accessed and controlled with pure RESTful APIs and they usually have a 1:1 mapping with the database actions - POST for create, GET for read, PUT/PATCH for update and DELETE for deleting.
Transaction
These are immutable datasets. Once added, no data can be changed or deleted. These are ideal for storing transactional activities like book keeping, virtual currencies, etc. They also feature a non-serial primary key or unique key (a random string or UUID or equivalent) so that the sequence cannot be predicted.
It only has CR (create-read) access, though in certain cases, minor updates are allowed which do not drastically affect the transactional data.
Access to the data is usually access controlled and isolated for users. Users cannot access each others’ data and can neither modify their own. Usually RESTful practices are limited because of limited data access.
Log
These tables are used for recording change in data for a given table. Similar to transaction tables, these are immutable. They denote the history of value a field at a given point in time. Common use cases are value transitions, changelog, etc.
Like transaction tables, these are strictly CR (create-read). Occasionally, they also contain information about the user who made the change and reason for change.
| _id | profile_id | field | value | user_id | comment | timestamp |
|---|---|---|---|---|---|---|
| 12 | 39 | status | verified | 23 | User marked as verified after validating email. | 2019-01-02T15:32:12Z |
| 28 | 39 | first_name | Tejas | 23 | User changed profile information. | 2019-01-02T17:11:54Z |
| 31 | 39 | status | active | 23 | User activated after completing profile. | 2019-01-02T17:13:01Z |
Unlike previous tables, data from table is never directly exposed to the user. Thus there are no RESTful endpoints. Though they are seldom used for administrative purposes to track user activity and troubleshoot issues. Access for such purposes is usually done using a server-side rendered page or using custom internal APIs which are very specific to the use case.
Mapping
These are only used for creating a relations between two or more tables. Such tables are often called many-to-many mapping tables, though, using appropriate UNIQUE keys, they can be restricted to many-to-one or even one-to-one mapping. They only store foreign keys to related tables. A good example would be assigning multiple tags to multiple articles.
Based on the behavior of the related tables, mapping tables can be CRUD (create-read-update-delete) or CR (create-read) only. Like log tables, they can also contain additional data for each mapping.
| _id | order_id | sku_id | quantity | timestamp |
|---|---|---|---|---|
| 9 | 87 | 12 | 1 | 2019-01-02T12:09:35Z |
| 10 | 87 | 5 | 5 | 2019-01-02T12:09:58Z |
| 15 | 91 | 12 | 2 | 2019-01-02T13:01:19Z |
Data from mapping tables is never exposed directly to the end user. It’s always indirectly used along with data from content tables and related data is shown in nested fashion. Pure RESTful APIs will usually nest the related data.