1

It's been a long time since I studied relational design, but I had a vague memory that it encourages not splitting a table unnecessarily. For instance, given the functional dependencies

K -> A
K -> B
K -> C

my assumption was that the "best" schema is just {KABC} and not something like {KAB, KC} or even {KA, KB, KC}. At least in practice that is how I've seen database designers implement the table.

However, a quick refresher on Wikipedia indicates that the normalization formalism

  • doesn't make any statement in the direction of obtaining a "minimal schema",
  • 6NF would even require {KA, KB, KC}. Since 6NF implies the other normal forms, it implies that it is even impossible for them to make such a minimal requirement.

I'm a bit confused that I got this wrong all the time. Does the notion of "obtaining a minimal number of tables" really play no role in formal relational design, and it is just common practice?

4
  • Perhaps the minimal property was actually a property of one of the standard algorithms that covert a given denormalized schema into e.g. 3NF (synthesis algorithm or decomposition algorithm or how they were called)? If 3NF has this degree of freedom it would make sense to design an algorithm in a way that it deterministically outputs the minimal schema.
    – bluenote10
    Mar 6, 2021 at 13:52
  • The q's "unnecessarily" is really a matter of opinion. It is not 'necessary' to go as far as 6NF. I do think it's necessary when designing a physical schema to understand what the 6NF form would be, in order to understand the tradeoffs vs a less normalised design. But no it has never been good practice to aim for "obtaining a minimal number of tables": usually there's a downside in update anomalies.
    – AntC
    Mar 6, 2021 at 22:48
  • @AntC If you look at it purely from an entity relationship modeling perspective it is unnecessary. All textbook ER modelling examples I've seen map entities to tables 1:1. For instance, given a clear-cut entity like User, there is no compelling reason from a design perspective to break it up into UserAddress, UserBirthday, UserContactInformation, etc. (from theoretical perspective of course, ignoring practical performance implications).
    – bluenote10
    Mar 7, 2021 at 8:17
  • Splitting up entity attributes is indicated if there is a temporal consideration: a User's Birthday is relatively static; their Address changes from time to time; their ContactInformation might change relatively often. Furthermore in real business (as opposed to in textbooks) an organisation will want to keep a history of such changes.
    – AntC
    Mar 7, 2021 at 8:57

3 Answers 3

4

The “Normal Forms” are narrowly defined in terms of eliminating redundant data and “update anomalies”. Whether fixing other schema design problems counts as “Normalization” can be debated, but in general parlance Normalization just means ensuring the database complies with some Normal Form.

enter image description here

5
  • 1
    Interestingly Wikipedia defines denormalization as the process of trying to improve the read performance of a database, at the expense of losing some write performance, by [adding redundant copies of data] or by grouping data. Splitting a table may fall under the latter category, which is perhaps why it always felt like a kind of denormalization to me. But apparently one that isn't covered by the formalism.
    – bluenote10
    Mar 6, 2021 at 13:47
  • 1
    @bluenote10 I would argue that statement is so poorly written and generic that it's borderline wrong, unfortunately. Denormalization could improve read performance and just as easily it could hurt it, it depends on the situation and use cases. Normalization helps ensure that the minimum amount of data is needed to provide for a given set of queries by reducing data redundancy, and reducing the size of your tables (at the row and page level). By only pulling back the fields you actually need through a few joins, querying can be faster than in a denormalized database, because of...
    – J.D.
    Mar 6, 2021 at 14:49
  • 1
    ...the reduced amount of data that needs to be located and loaded off disk. Whereas the same query against a denormalized table that is 300 fields wide would be much less efficient because the row size and number of pages that row lives in much larger. Conversely, in other use cases, such as certain OLAP warehouses, it can be faster to have the fact fields that correlate to your measures within the same table, to minimize the work of joining to other tables to get those facts. It may depend on the actual database system you use and whether they're designed to handle normalized data...
    – J.D.
    Mar 6, 2021 at 14:53
  • 1
    ...as well, since some systems aren't as optimized with large numbers of joins. So in practice at least, the performance benefits of denormalization are very situational. There are pros and cons to read performance for both strategies depending on your actual use cases.
    – J.D.
    Mar 6, 2021 at 14:54
  • 1
    "Normalization" is a basic math term & Codd had a BA & MA in math & a MSc & PhD in CS & that quote is a math joke.
    – philipxy
    Mar 6, 2021 at 17:49
1

Reducing the number of tables isn’t a goal, either in design theory or in practice. Reducing tables can help or hurt performance, which is why in practice people increase or reduce tables (regardless of duplicate data).

Roughly speaking, increasing the number of tables is helpful when you have data that is rarely used in conjunction, decreasing the number of tables is helpful when you have data that is frequently used in conjunction. Relational theory doesn’t care how fast or slow something is. Theory keeps you from getting into invalid states, practically speaking, some invalid states may be acceptable as long as everything comes out right in the end.

1

To add to the other answers about normalization, outside of theory on how a database should be structured to represent the data, there can be practical considerations that might make splitting a table sensible.

One possible implementation of ACID you would find in Postgres, for example, involves deleting and reinserting a row on update and later reclaiming the deleted rows through vacuuming. So if you have a table that might contain a lot of mostly-read data and a dirty bit to mark rows for processing, splitting the dirty bit into a separate table can significantly improve performance since UPDATEs need to rewrite a fraction of the data. It can also save you loads of disk space since the single table case would keep duplicating data until a VACUUM can reclaim the dead tuples.

Similar problems can occur when part of the data is so big it gets TOASTed, so you might want to split commonly accessed or updated fields into a separate table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.