Tejas Jadhav

Common database table design patterns

January 05, 2019

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.

_idprofile_idfieldvalueuser_idcommenttimestamp
1239statusverified23User marked as verified after validating email.2019-01-02T15:32:12Z
2839first_nameTejas23User changed profile information.2019-01-02T17:11:54Z
3139statusactive23User activated after completing profile.2019-01-02T17:13:01Z
Example: Log table for tracking user profile changes

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.

_idorder_idsku_idquantitytimestamp
9871212019-01-02T12:09:35Z
1087552019-01-02T12:09:58Z
15911222019-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.


Written by Tejas Jadhav full time software engineer

© 2021 Tejas Jadhav