The principles of database design, or, the Truth is out there May 17, 2025 by Eduardo Bellani

Every software project needs to represent the reality of the business he is embedded in. The way we can represent reality as limited rational beings is through propositions, i.e, declarative statements that affirm or deny something about reality. When a collection of such propositions is stored in a computer system, we call it a database.

Such database needs to be designed to properly reflect reality. This can’t be automated, since the semantics of the situation need to be encoded in a way that can be processed by a computer. Such then is the goal of database design: to encode propositions in such a way that can properly be processed by a database management system (DBMS).

At this point, a regular software developer comes to a stall. Since there is scarcely any formal training in database design (or formal logic) in his education, he tends to fall back haphazardly on ad-hoc methods, with severe consequences (update anomalies and data inconsitencies with huge potential downsides).

If you are such developer, you need to understand the underlying principles of database design. Think about it, if you don’t have principles of design, you are not doing engineering, are you?

Here is a list of design principles to follow for formal database design(McGoveran 2012, 2015)(Pascal 2016):

• Principle of Orthogonal Design (POOD): Base relations are independent;
• Principle of Representational Parsimony (PORP): There are no superfluous base relations;
• Principle of Expressive Completeness (POEC): All meaningful relations are derivable from the base relations.
• Principle of Full Normalization (POFN) : Every base relation should be in its highest normal form (3, 5 or 6th normal form).​ Thus eliminating redundancy and preventing anomalies by ensuring that each relation is free from undesirable characteristics like partial, transitive, or join dependencies.
• The Information Principle (TIP) : All information in the database is represented explicitly and in exactly one way — by attribute values drawn from domains in relations.
• Principle of Logical Independence (PLI) : Application programs and terminal activities remain logically unimpaired when information preserving changes of any kind that theoretically permit unimpairment are made to the base relations.

To these I’d like to introduce a new principle, an innovation I hope to develop in future work:

• Principle of Essential Denotation (PED): A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity — not by arbitrary or surrogate values.

The following pseudo-SQL shows the contrast between improper and proper denotation:

  -- usage of surrogate keys
  create table citizen (
    id uuid primary key,
    national_id text,
    full_name text);

Code Snippet 1: Illustration of not using PED with SQL

This usage has some problems, the worst of it is the disconnection between database structure and domain semantics. Here is a rework that preserves such connection:

  create domain national_id as text check (...);

  create table citizen (
    national_id national_id primary key,
    full_name text);

Code Snippet 2: Illustration of using PED with SQL

Conclusion

Databases are representations of reality, and as such, they are foundational to any serious information system. Poor design leads to semantic confusion and technical instability—with consequences that can be costly and far-reaching. Good design demands rigor, discipline, and a firm grasp of foundational principles.

To put it simply: if you’re in the business of information, you need to know how to build structures that tell the truth that is out there.

References