One Table for Many Organisation Types
A single organisations table with nullable per-type columns is cheaper to maintain than one table per type, if you accept three consequences deliberately.
TL;DR
A public service portal needed to support a new institution type. The decision was made to use a single generic table with nullable columns. This approach is known as Single Table Inheritance.
I still remember the deploy that forced the decision. The public service portal I work on already carried several kinds of institution, and that night a new kind arrived that needed columns of its own: term of office for the board and a display order. Those columns existed nowhere, and there were only two options, add a table for that type or widen the table already there.
My first guess was one table per type. It looked tidy at the start: each type held its own columns, nothing sat empty. What I failed to account for is that the institution list is almost always needed as a single list, and once types multiply the list query turns into a chain of UNION or nested joins. One listing page that used to be one query became five.
One Table for Every Type
The decision landed on a single generic table. The organizations table carries every column any type owns, and columns that concern only some types stay nullable:
CREATE TABLE organizations (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_id BIGINT UNSIGNED NOT NULL,
nama VARCHAR(200) NOT NULL,
nama_panjang VARCHAR(500),
deskripsi TEXT,
kontak VARCHAR(100),
alamat TEXT,
masa_bakti VARCHAR(20),
sk_habis TINYINT(1) NOT NULL DEFAULT 0,
tipe_cabor VARCHAR(30),
no_urut INT,
status ENUM('AKTIF','NONAKTIF') NOT NULL DEFAULT 'AKTIF',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_organizations_category FOREIGN KEY (category_id) REFERENCES categories(id)
);The pattern has a name: Single Table Inheritance, one table holding columns for all the fields of the various classes, and its main selling point is that the join count stays small [4]. Because the type is no longer implied by a table name, the type is bound through a relation to the category table, while a genuinely closed vocabulary such as status is fine as an ENUM whose permitted values are fixed when the table is created [3].
Board members go into a child table, not a JSON column on the organisation row:
CREATE TABLE organization_members (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
organization_id BIGINT UNSIGNED NOT NULL,
jabatan VARCHAR(100) NOT NULL,
nama VARCHAR(200) NOT NULL,
urutan INT NOT NULL DEFAULT 0,
CONSTRAINT fk_organization_members_org FOREIGN KEY (organization_id)
REFERENCES organizations(id) ON DELETE CASCADE
);The down migration has to respect order too: organization_members is dropped first, then organizations, because the reference direction must not be reversed.
Three Consequences Accepted Deliberately
Validation moves into the application. The database only knows nullable columns; it does not know that a term-of-office column is meaningless for an institution without a rotating board. Per-type checks become the service's job, including rejecting combinations of columns that make no sense for a given type.
Referential actions must be written, not assumed. An omitted referential action means NO ACTION, and RESTRICT behaves the same way in InnoDB, rejecting deletion of the parent while children remain [1]. Without ON DELETE CASCADE on the member table, the delete button for an organisation fails with a foreign key constraint error instead of clearing its members. One detail that surprises people: a CASCADE action does not activate triggers, so any logic placed on the child table's trigger will not run on a parent delete [1].
The seed must be idempotent. I wrote the migration that prepares the initial category list as one multi-row INSERT with ON DUPLICATE KEY UPDATE, so a redeploy, a second environment, or a manual repair does not halt on a duplicate key [2]. Its affected-rows value doubles as speedy evidence in the migration log: one means a new row, two means the old row was updated as well [2].
Two things I tidied up once the table was live: writing an organisation together with replacing its member list now runs inside a single transaction, so an organisation is never stored without its board when one of the member inserts fails. Then the public listing filters to active status only, and the delete and update responses were made uniformly 404 for a missing id. That uniformity follows from the nature of the HTTP methods themselves, PUT and DELETE are idempotent so a repeated request for the same id should not produce a different side effect [5].
The Rule for Picking a Table Shape
The rule I use now is simple: first count how many types share roughly 80 percent of their columns. If most of them do, one generic table with extra nullable columns is the cheaper thing to maintain, especially because the combined listing stays a single query [4]. If each type has almost no column in common, separate tables are the more honest choice.
The second condition: nullable columns may only stay while the type vocabulary is still growing. Once the list of types stops expanding and a column is used by exactly one type, that is the signal the column deserves its own table rather than hanging in the shared one forever.
Sources: