14 ms·

The principles of database design, or, the Truth is out there

121 points by b-man 1y ago

AnonHP 1y ago
Seems like this article places too much emphasis on normalization, which is appropriate for many cases, but may be a huge cost and performance issue for requirements like reporting. You may probably need different kinds of schema and data storage structures for different requirements in the same application, which in turn may result in duplicated data, but results in acceptable trade offs.
- deleted 1y ago
  [deleted]
- weinzierl 1y ago
  " Every base relation should be in its highest normal form (3, 5 or 6th normal form). " If I remember my database lessons correctly there is no strictly highest normal form. It progresses from 1NF to BCNF, but above that it is more choosing different trade-offs. Even below it is always a trade-off with performance and that is why we most of the time aim for 3NF, and sometimes BCNF.
  - moi2388 1y ago
    That’s what I was taught as well. And even then I use it more as a rule of thumb
  - plank 1y ago
    There are big disadvantages from choosing e g. 5th normal form: any changing in business requirements leads to a big rewrite and data conversion. Never seen successful projects choosing beyond 3rd/BCNF.
- zeroCalories 1y ago
  Putting aside performance implications, I get kinda irritated by having to do joins for basic queries all the time.
  - sgarland 1y ago
    Then don't use a relational database. Sorry for being rude, but joins are an integral part of the relational model. If you want a KV store, you should use a KV store; if you want a Document DB, you should use a Document DB.
    - zeroCalories 1y ago
      Sometimes I just wanna do some adhoc analysis and I need to do like four joins because some monkey decided to split everything into it's own table without thinking about the people that would actually have to use the db.
      - sgarland 1y ago
        Your one-off analysis doesn’t trump the normal OLTP workloads.
        zeroCalories 1y ago
        I regularly have to do queries. I'm trying to understand my non-trivial product to figure out what work items will have, or have had, impact. Sometimes I'm in a meeting and need to do some real time analysis. My ability to understand and use the data is actually more important than a slightly higher database bill. And I thought we were ignoring the poor performance of normalization?
  - datadrivenangel 1y ago
    If you're doing reporting, create a reporting view that does all those joins once!
adamcharnock 1y ago
> A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity — not by arbitrary or surrogate values. I fairly strongly disagree with this. Database identifiers have to serve a lot of purposes, and natural key almost certainly isn’t ideal. Off the top my head, IDs can be used for: - Joins, lookups, indexes. Here data type can matter regarding performance and resource use. - Idempotency. Allowing a client to generate IDs can be a big help here (ie UUIDs) - Sharing. You may want to share a URL to something that requires the key, but not expose domain data (a URL to a user’s profile image shouldn’t expose their national ID). There is not one solution that handles all of these well. But using natural keys is one of the least good options. Also, we all know that stakeholders will absolutely swear that there will never be two people with the same national ID. Oh, except unless someone died, then we may reuse their ID. Oh, and sometimes this remote territory has duplicate IDs with the mainland. Oh, and for people born during that revolution 50 years ago, we just kinda had to make stuff up for them. So ideally I’d put a unique index on the national ID column. But realistically, it would be no unique constraint and instead form validation + a warning on anytime someone opened a screen for a user with a non-unique ID. Then maybe a BIGINT for database ID, and a UUID4/7 for exposing to the world. EDIT: Actually, the article is proposing a new principle. And so perhaps this could indeed be a viable one. And my comment above would describe situations where it is valid to break the principle. But I also suspect that this is so rarely a good idea that it shouldn’t be the default choice.
- Jarwain 1y ago
  Why have both a database ID and UUIDv7, versus just a UUIDv7?
  - sroussey 1y ago
    There is a security principle to not expose real identifiers to the outside world. It makes a crack in your system easier to open.
    - Jarwain 1y ago
      Idk that reeks of security through obscurity to me. Your authorization/permission scheme has got to be fubar'd if you're relying on obscuring IDs to prevent someone from accessing a resource they shouldn't. I'm sure I'm missing something obvious, but I'm not sure what other threat vectors there are, assuming it's in conjunction with other layers of security like a solid authorization/access control scheme. I guess I'm not the biggest fan for a few reasons. I'd rather try and design a system such that it's secure even in the case of database leak/compromise or some other form of high transparency. I don't want to encourage a culture of getting used to obscurity and potentially depending on it instead of making sure that transparency can't be abused. Also, it just feels wasteful. If you have two distinct IDs for a given resource, what are you building your foreign keys against? If you build it against the hidden one, and want to query the foreign table based on user input, you've gotta either do a join or do a query to Get the hidden key just to do another query. It just feels wasteful. EDIT: apparently RFC 4122 even says not to assume UUIDs are hard to guess and shouldn't be used for security capabilities. So if it shouldn't be depended on for security, why add all this complexity to keep it secure?
madduci 1y ago
Many of the principles and also the example provided for PED cannot be mapped easily through an ORM library and AFAIK Java JPA doesn't handle it too. Why does it matter? I have seen that many developers rely totally only on the code to manage entities on the database, instead of relying on prepared statements and pure SQL queries. This obviously opens a door for poor optimisation, since these Entity Management libraries don't support certain SQL capabilities.
- jbverschoor 1y ago
  That’s non argument. Just use a better ORM. Hibernate is able to do that for about 20 years. That said, I’m not a fan of natural keys as primary keys. Especially composite keys. This just takes everybody back to the 80s/early 90s. It only makes sense when there’s a huge storage benefit
  - javcasas 1y ago
    Find me an ORM that can do: * window functions * SELECT FOR UPDATE * row-level security Prepared statements and query builders are the better ORM.
mrkeen 1y ago
> 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. create table citizen ( national_id national_id primary key, full_name text); Is national_id really a natural key, or is it someone else's synthetic key? If so, should the owner of that database have opted for a natural key rather than a synthetic key? More arguments for synthetic over natural keys: https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-keys/ https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-...
- tuatoru 1y ago
  The "natural key" for a (natural) person is compound: full name and mother's full name, plus date, time and place of birth. Your birth certificate is your primary identification document. However that still runs into problems of nondurability of the key in cultures that delay naming for a few years. To name one problem. So yeah, use a big enough integer as your key, and have appropriate checks on groups of attributes like this. However, if you are only interested in citizens, then a "natural" key is the citizen id issued by the government department responsible for doing that. (Citizen is a role played by a natural person so probably doesn't have too many attributes.) I still wouldn't use that as a primary key on the citizen table, though.
  - bouke 1y ago
    That natural key isn’t guaranteed to be unique.
    - b-man 1y ago
      Depends on the universe of discourse adopted.
  - stevoski 1y ago
    I know someone who doesn’t know when she was born, nor who her mother is. She doesn’t have a birth certificate. She was born in a country that was enduring several years of brutal war. I know another person whose national ID was changed. Systems that use national ID as primary key failed to accept this change.
weinzierl 1y ago
" Every base relation should be in its highest normal form (3, 5 or 6th normal form). " If I remember my database lessons correctly there is no strictly highest normal form. It progresses from 1NF to BCNF, but above that it is more choosing different trade-offs. Even below it is always a trade-off with performance and that is why we most of the time aim for 3NF, and sometimes BCNF.
xlii 1y ago
I have a joke in context that I often like to tell: Devil captured Physicist, Engineer and Mathematician. He gave each of them big can of spam and locked them in the empty room saying „you will be here for 2 weeks - open the can and survive or die to starvation”. After 2 weeks Devil opens Physicist cell. It’s covered floor to the ceiling in complex scribbles. One piece of wall is clean of etching but small dent is visible. Can of span is opened and eaten clean, Physicist sits in corner visibly annoyed. Next one is Engineer. Cell walls are covered in multiple dents and pieces of spam. Engineer is bruised almost as much as the can, but it is ultimately opened and engineer is alive. Finally the Devil opens Mathematician cell and find him dead. Only „given the cylinder” is etched on the wall. —- Puent isn’t about engineering but it always helped me to set limits between software engineering and computer science.
- Terr_ 1y ago
  Uh... I don't get it. Perhaps something was lost in translation? It sounds like you're saying each person had to smash a container against the wall to release the food, and the Mathematician got side-tracked analyzing the container. However: 1. Spam™ cans have a convenient pull-tab, you don't need to bash them against a wall. 2. Spam™ cans are typically not "cylinder" shaped... Unless the Mathematician was expecting a cylinder and didn't recieve one, but in that case what did the Devil say that would have caused that expectation? 3. Why would the Engineer do worse at opening the can compared to the Physicist?
  - xlii 1y ago
    It could be. Spam is translation for any kind of minced meat, there’s no implication about way of opening, shaper or brand. The one that was given out was (from scribble) without tab and cylindrical in shape. And since you decided to query I’ll share another (more local) joke. In Poland you can buy spam called „Konserwa Turystyczna” (which translates to tourist can roughly). And the running joke about it is that you can (psst) buy one while NOT being a tourist. Nobody checks that! As for the 3rd. Nobody said it was worse, that’s the point. I like the joke because it highlights it. The goal wasn’t to do it perfectly. It was to survive. Engineer succeeded in a way that solved the given problem. And in IT we have a saying that premature optimization is a root of all evil. Doesn’t that sound aligning? :)
moi2388 1y ago
“ Principle of Full Normalization (POFN) : Every base relation should be in its highest normal form (3, 5 or 6th normal form)” No it shouldnt.
- JSR_FDED 1y ago
  Please, feel free to elaborate so we can all learn
  - threeseed 1y ago
    It's ideological purity which has no place in the real world. Fully normalised structures are slow, dangerous and place expensive and frustrating burdens on upstream users reducing the amount of value that can be extracted from the data. I've been working around data for 20+ years and not once seen a dataset where tables could be blindly joined without some conditions. In these situations it can be better to just pre-join them so the business logic is captured. You want to be pragmatic and have a healthy and constructive mix.
    - HelloNurse 1y ago
      Since I "want to be pragmatic" I want my data not repeated, so that it cannot be inconsistent, and not NULL, to simplify logic. And of course any interesting join condition involves fields from different tables.
- bwfan123 1y ago
  I have implemented database schemas, (without knowing database theory), and these principles are a revelation to me. A question I have is: Given a schema, are there automated verifiers for validating that it adheres to these principles ? A schema "linter" of sorts. There seem to be parallels to linear algebra here (orthogonal bases, decompositions, etc)
gitroom 1y ago
[dead]
pretoriusdre 1y ago
I really don't like using natural keys as primary keys. Natural keys sometimes need to change for unforeseen reasons, such as identity theft, and this is really tricky to manage if those keys are cascaded into many tables as foreign keys. Natural keys are often not unique either. Using the national ID example, there are millions of duplicate SSNs issued within USA. https://www.computerworld.com/article/1687803/not-so-unique.html https://www.computerworld.com/article/1687803/not-so-unique.... So, don't use natural keys as primary keys. Put them in as surrogate keys, ideally with a unique constraint.
jiggawatts 1y ago
The "natural ID" for people design reminds me of a story from a state department of education: They had two students, both named John Smith Jr. They were identical twins and attending the same class. They had the same birth date, school, parents, phone number, street address, first name, last name, school, teachers, everything... The story was that their dad was John Smith Sr in a long line of John Smiths going back a dozen generations. It was "a thing" for the family line, and there was no way he was going to break centuries of tradition just because he happened to have twins. Note: In very junior grades the kids aren't expected to memorise and use a student ID because they haven't (officially) learned to read and write yet! (I didn't use one until University.)
- Pamar 1y ago
  Same first name? For twins? I find it very difficult to believe this is not prevented by some law.
  - jiggawatts 1y ago
    Me too, but it seems to be an oddly common occurrence: https://www.google.com/search?q=identical+twins+with+the+same+name https://www.google.com/search?q=identical+twins+with+the+sam...
jandrewrogers 1y ago
This takes an overly simple view of what domains can look like. There are data models that necessarily violate these principles, and they aren’t all that rare. Some examples: > A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity In some domains there is no natural key because the identity is literally an inference problem and relations are probabilistic. The objective of the data model is to aggregate enough records to discover and attribute natural keys with some level of confidence. A common class of data models with this property are entity resolution data models. > All information in the database is represented explicitly and in exactly one way Some data models have famously dual natures. Cartographic data models, for example, must be represented both as a graph models (for routing and reachability relationships) and as geometric models (for spatial relationships). The “one true representation” has been a perennial argument in mapping for my entire life and both sides are demonstrably correct. > Every base relation should be in its highest normal form (3, 5 or 6th normal form). This is one of those things that sounds attractive because it ignores that it requires no ambiguities about domain boundaries or semantics, which doesn’t exist in practice. I bought into this idea too when I was a young and naive data modeler. Trying to tamp out these ambiguities adds an unbounded number of data model epicycles that add a lot of complexity and performance loss. At some point, strict normalization is not worth the cost in several aspects. In almost all cases, it is far more important that the data model be efficient to work with than it be the abstract platonic ideal of a domain model. All of these principles have to work on real hardware in real operational environments with all of the messy limitations that implies.
- Akronymus 1y ago
  I strive to keep most things in at least the 3rd NF. Except stuff like addresses or names, those I intentionally don't push to be normalized, as they make up a single datum anyways, and normalizing creates more problems than it solves IME.
  - friendzis 1y ago
    Addresses and names are nice, well-known examples for cross-domain data. It's not that attempts at normalizing these structural datums create problems per se, but rather there is no single true normalization, therefore wrong normalizations start causing problems.
peanut-walrus 1y ago
No. Real life rarely has natural keys that are unique and do not change. For example the national id number in several countries can change in some circumstances...and that is already a synthetic key.
stevoski 1y ago
Bad luck if you don’t yet have (or know) your national ID. National id is not something issued at birth in the country I live in. It’s something applied for at a certain age.
- sitharus 1y ago
  Where I live there’s no such thing as national ID. There’s a few documents that can be used as such depending on the purpose, and some of those change the number on every update! Never trust something outside your system to be stable.
cess11 1y ago
"Databases are representations of reality" "tell the truth that is out there" Both truth and representation are very slippery, many-faceted concepts, encumbered with millennia of use and philosophy. Using them in this way is deceptive to the junior and useless to the senior.
msla 1y ago
The example works if and only if there's one National ID per person. That's not true for SSNs. It's not true in that it is false. My statement that it is false is, in point of fact, true, and therefore not up for debate. The government even acknowledges this: https://www.ssa.gov/OP_Home/handbook/handbook.14/handbook-1401.html https://www.ssa.gov/OP_Home/handbook/handbook.14/handbook-14... > 1401.7 Can a person have more than one SSN? > Most persons have only one SSN. In certain limited situations, SSA can assign you a new number. If you receive a new SSN, you should use the new number. However, your old and new number will remain linked in our records to ensure that your earnings are credited properly. This could affect your benefits. Maybe there are countries where it is the case that nobody ever gets multiple National IDs. Maybe there are countries without fraud and where everyone can and will update their records when the government does. Maybe there is a veritable Utopia on Earth, a Cockaigne of validated data and reasonable deadlines.
deleted 1y ago
[deleted]
khana 1y ago
[dead]
photios 1y ago
"Databases are representations of reality" The national ID example is funny. Let me give you a dose of reality concerning national IDs. - I've seen cases with my country's national ID numbers containing duplicates due to human error. - National ID's for people can change. In my country, children being adopted, get a different ID after adoption. - There exist people without a national ID. Or people that don't want you to know their national ID.
- HelloNurse 1y ago
  Don't forget general patterns, e.g. you positively need to enter a record right now, but they didn't tell you what "key" to use; or they misspelled the "key" and you have to update it by user request.
traches 1y ago
Ohhhhh absolutely not, thank you. I want my IDs to have absolutely no meaning whatsoever.
deleted 1y ago
[deleted]
RedShift1 1y ago
Article sounds like it was written from a purely theoretical perspective and not from what happens and the requirements in real life.
exabrial 1y ago
I’m going to have to disagree. What happened what national ids are expanded to include letters? Or extra digits? I’ve lived through this mess a few times in the migrations are painful and take a ton of time away from solving actual business problems. For example: us telephone numbers. The exchange (middle 3 digits), used to physically Geolocate a consumer, because everybody had land lines. I worked on a project and an investment firm that was using it as a primary key to identify a users’s location. Holy old macro was that an unbelievably expensive and painful migration. Don’t do this. Instead, follow this principle: never ever use an externally assigned identifier as a primary key in your database. Instead: link tables with simple integers. Do not use these integers for any other purpose and assume unordered. Never let these integers leave the app. When exchanging information between systems or third parties generate a suitable external identifier with a alphabetical prefix.
al2o3cr 1y ago
Seems like a terrible idea, TBH - the reality is that values like "national ID" can be sensitive themselves. In the US, such a scheme would make even HTTP ACCESS LOGS into personally-identifiable-information requiring special handling.
datadrivenangel 1y ago
Principle of Full Normalization is a useful guide that should not be religiously followed. Mostly full normalization is good, but some objects like names and address resist normalization and thus can introduce significantly more complexity than they save by normalizing.
mannyv 1y ago
All of these "rules" will change once they hit the reality of utilization. It reminds me of this: "We need to normalize the database for better performance." "We need to denormalize the database for better performance."
QuiCasseRien 1y ago
> A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity — not by arbitrary or surrogate values. That's a very nice example of Theory VS Practice ! I agree, in Theory, PK should be identified by the natural unique key identifier of the domain. Your example with national_id is proper. In practice : - due to their varying underlying type, manipulating natural key identifier throught an application (passing values) is not always easy. UUID or INT are reliable and always works. - sometimes, we want a "poor" anonymization when passing and calculting data. As the PK is the identifier of the tuple, having a personal data for PK can be problematic. - last but not least : using PK that is subject to change is a terrible idea. An address mail, for example, anybody can be subject to change. When it's the PK, you are fuck to do or propagate the change. And in this age of gods war, Practice always win.
b-man 1y ago
Lots of peole got hung up on the example, which I thought would be be helpful on the discussion, but certainly should not replace the main point, which is: Relations, attributes and tuples are logical. A PK is a combination of one or more attributes representing a name that uniquely identifies tuples and, thus, is logical too[2], while performance is determined exclusively at the physical level, by implementation. So generating SKs for performance reasons (see, for example, Natural versus Surrogate Keys: Performance and Usability, Performance of Surrogate Key vs Composite Keys) is logical-physical confusion (LPC)[3]. Performance can be considered in PK choice only when there is no logical reason for choosing one key over another. https://www.dbdebunk.com/2018/04/a-new-understanding-of-keys-part-3.html https://www.dbdebunk.com/2018/04/a-new-understanding-of-keys...
idoubtit 1y ago
Just a side note about the historical anecdote at the bottom of the post, which is related to Notre-Dame de Paris: > 28 statues that portrayed the biblical Kings of Judah. [...] They didn’t portray French kings That's wrong. Several texts from the revolution and before still exist that prove that these kings were identified as both Judea kings and France kings. For instance, David was Pépin le bref. On one of the gates of the cathedral, the List of the French kings was engraved, starting with Clovis. That glorification of the monarchy, with parallels to the bible, was common at the time: other French medieval cathedrals show the same analogies.
- b-man 1y ago
  Thanks, I didn't knew that. Can you link to one such text?
thuanao 1y ago
> When a collection of such propositions is stored in a computer system, we call it a database. Is it? A database is a place data is stored and retrieved. It is literally a data base. No more, no less. Whether it logically models a domain may be completely irrelevant to store and retrieve data. I would argue that a database should not logically model a domain. Why? Because every database must store and retrieve data. Therefore data should be modeled to store and retrieve data as efficiently as possible. And the structure that most efficiently stores and retrieves data most likely does not logically model a domain.

121 points by b-man 1y ago

Seems like this article places too much emphasis on normalization, which is appropriate for many cases, but may be a huge cost and performance issue for requirements like reporting. You may probably need different kinds of schema and data storage structures for different requirements in the same application, which in turn may result in duplicated data, but results in acceptable trade offs.

deleted 1y ago
[deleted]
weinzierl 1y ago
" Every base relation should be in its highest normal form (3, 5 or 6th normal form). " If I remember my database lessons correctly there is no strictly highest normal form. It progresses from 1NF to BCNF, but above that it is more choosing different trade-offs. Even below it is always a trade-off with performance and that is why we most of the time aim for 3NF, and sometimes BCNF.
- moi2388 1y ago
  That’s what I was taught as well. And even then I use it more as a rule of thumb
- plank 1y ago
  There are big disadvantages from choosing e g. 5th normal form: any changing in business requirements leads to a big rewrite and data conversion. Never seen successful projects choosing beyond 3rd/BCNF.
zeroCalories 1y ago
Putting aside performance implications, I get kinda irritated by having to do joins for basic queries all the time.
- sgarland 1y ago
  Then don't use a relational database. Sorry for being rude, but joins are an integral part of the relational model. If you want a KV store, you should use a KV store; if you want a Document DB, you should use a Document DB.
  - zeroCalories 1y ago
    Sometimes I just wanna do some adhoc analysis and I need to do like four joins because some monkey decided to split everything into it's own table without thinking about the people that would actually have to use the db.
    - sgarland 1y ago
      Your one-off analysis doesn’t trump the normal OLTP workloads.
      - zeroCalories 1y ago
        I regularly have to do queries. I'm trying to understand my non-trivial product to figure out what work items will have, or have had, impact. Sometimes I'm in a meeting and need to do some real time analysis. My ability to understand and use the data is actually more important than a slightly higher database bill. And I thought we were ignoring the poor performance of normalization?
- datadrivenangel 1y ago
  If you're doing reporting, create a reporting view that does all those joins once!

adamcharnock 1y ago

> A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity — not by arbitrary or surrogate values. I fairly strongly disagree with this. Database identifiers have to serve a lot of purposes, and natural key almost certainly isn’t ideal. Off the top my head, IDs can be used for: - Joins, lookups, indexes. Here data type can matter regarding performance and resource use. - Idempotency. Allowing a client to generate IDs can be a big help here (ie UUIDs) - Sharing. You may want to share a URL to something that requires the key, but not expose domain data (a URL to a user’s profile image shouldn’t expose their national ID). There is not one solution that handles all of these well. But using natural keys is one of the least good options. Also, we all know that stakeholders will absolutely swear that there will never be two people with the same national ID. Oh, except unless someone died, then we may reuse their ID. Oh, and sometimes this remote territory has duplicate IDs with the mainland. Oh, and for people born during that revolution 50 years ago, we just kinda had to make stuff up for them. So ideally I’d put a unique index on the national ID column. But realistically, it would be no unique constraint and instead form validation + a warning on anytime someone opened a screen for a user with a non-unique ID. Then maybe a BIGINT for database ID, and a UUID4/7 for exposing to the world. EDIT: Actually, the article is proposing a new principle. And so perhaps this could indeed be a viable one. And my comment above would describe situations where it is valid to break the principle. But I also suspect that this is so rarely a good idea that it shouldn’t be the default choice.

Jarwain 1y ago
Why have both a database ID and UUIDv7, versus just a UUIDv7?
- sroussey 1y ago
  There is a security principle to not expose real identifiers to the outside world. It makes a crack in your system easier to open.
  - Jarwain 1y ago
    Idk that reeks of security through obscurity to me. Your authorization/permission scheme has got to be fubar'd if you're relying on obscuring IDs to prevent someone from accessing a resource they shouldn't. I'm sure I'm missing something obvious, but I'm not sure what other threat vectors there are, assuming it's in conjunction with other layers of security like a solid authorization/access control scheme. I guess I'm not the biggest fan for a few reasons. I'd rather try and design a system such that it's secure even in the case of database leak/compromise or some other form of high transparency. I don't want to encourage a culture of getting used to obscurity and potentially depending on it instead of making sure that transparency can't be abused. Also, it just feels wasteful. If you have two distinct IDs for a given resource, what are you building your foreign keys against? If you build it against the hidden one, and want to query the foreign table based on user input, you've gotta either do a join or do a query to Get the hidden key just to do another query. It just feels wasteful. EDIT: apparently RFC 4122 even says not to assume UUIDs are hard to guess and shouldn't be used for security capabilities. So if it shouldn't be depended on for security, why add all this complexity to keep it secure?

madduci 1y ago

Many of the principles and also the example provided for PED cannot be mapped easily through an ORM library and AFAIK Java JPA doesn't handle it too. Why does it matter? I have seen that many developers rely totally only on the code to manage entities on the database, instead of relying on prepared statements and pure SQL queries. This obviously opens a door for poor optimisation, since these Entity Management libraries don't support certain SQL capabilities.

jbverschoor 1y ago
That’s non argument. Just use a better ORM. Hibernate is able to do that for about 20 years. That said, I’m not a fan of natural keys as primary keys. Especially composite keys. This just takes everybody back to the 80s/early 90s. It only makes sense when there’s a huge storage benefit
- javcasas 1y ago
  Find me an ORM that can do: * window functions * SELECT FOR UPDATE * row-level security Prepared statements and query builders are the better ORM.

mrkeen 1y ago

> 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. create table citizen ( national_id national_id primary key, full_name text); Is national_id really a natural key, or is it someone else's synthetic key? If so, should the owner of that database have opted for a natural key rather than a synthetic key? More arguments for synthetic over natural keys: https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-keys/ https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-...

tuatoru 1y ago
The "natural key" for a (natural) person is compound: full name and mother's full name, plus date, time and place of birth. Your birth certificate is your primary identification document. However that still runs into problems of nondurability of the key in cultures that delay naming for a few years. To name one problem. So yeah, use a big enough integer as your key, and have appropriate checks on groups of attributes like this. However, if you are only interested in citizens, then a "natural" key is the citizen id issued by the government department responsible for doing that. (Citizen is a role played by a natural person so probably doesn't have too many attributes.) I still wouldn't use that as a primary key on the citizen table, though.
- bouke 1y ago
  That natural key isn’t guaranteed to be unique.
  - b-man 1y ago
    Depends on the universe of discourse adopted.
- stevoski 1y ago
  I know someone who doesn’t know when she was born, nor who her mother is. She doesn’t have a birth certificate. She was born in a country that was enduring several years of brutal war. I know another person whose national ID was changed. Systems that use national ID as primary key failed to accept this change.

weinzierl 1y ago

" Every base relation should be in its highest normal form (3, 5 or 6th normal form). " If I remember my database lessons correctly there is no strictly highest normal form. It progresses from 1NF to BCNF, but above that it is more choosing different trade-offs. Even below it is always a trade-off with performance and that is why we most of the time aim for 3NF, and sometimes BCNF.

xlii 1y ago

I have a joke in context that I often like to tell: Devil captured Physicist, Engineer and Mathematician. He gave each of them big can of spam and locked them in the empty room saying „you will be here for 2 weeks - open the can and survive or die to starvation”. After 2 weeks Devil opens Physicist cell. It’s covered floor to the ceiling in complex scribbles. One piece of wall is clean of etching but small dent is visible. Can of span is opened and eaten clean, Physicist sits in corner visibly annoyed. Next one is Engineer. Cell walls are covered in multiple dents and pieces of spam. Engineer is bruised almost as much as the can, but it is ultimately opened and engineer is alive. Finally the Devil opens Mathematician cell and find him dead. Only „given the cylinder” is etched on the wall. —- Puent isn’t about engineering but it always helped me to set limits between software engineering and computer science.

Terr_ 1y ago
Uh... I don't get it. Perhaps something was lost in translation? It sounds like you're saying each person had to smash a container against the wall to release the food, and the Mathematician got side-tracked analyzing the container. However: 1. Spam™ cans have a convenient pull-tab, you don't need to bash them against a wall. 2. Spam™ cans are typically not "cylinder" shaped... Unless the Mathematician was expecting a cylinder and didn't recieve one, but in that case what did the Devil say that would have caused that expectation? 3. Why would the Engineer do worse at opening the can compared to the Physicist?
- xlii 1y ago
  It could be. Spam is translation for any kind of minced meat, there’s no implication about way of opening, shaper or brand. The one that was given out was (from scribble) without tab and cylindrical in shape. And since you decided to query I’ll share another (more local) joke. In Poland you can buy spam called „Konserwa Turystyczna” (which translates to tourist can roughly). And the running joke about it is that you can (psst) buy one while NOT being a tourist. Nobody checks that! As for the 3rd. Nobody said it was worse, that’s the point. I like the joke because it highlights it. The goal wasn’t to do it perfectly. It was to survive. Engineer succeeded in a way that solved the given problem. And in IT we have a saying that premature optimization is a root of all evil. Doesn’t that sound aligning? :)

moi2388 1y ago

“ Principle of Full Normalization (POFN) : Every base relation should be in its highest normal form (3, 5 or 6th normal form)” No it shouldnt.

JSR_FDED 1y ago
Please, feel free to elaborate so we can all learn
- threeseed 1y ago
  It's ideological purity which has no place in the real world. Fully normalised structures are slow, dangerous and place expensive and frustrating burdens on upstream users reducing the amount of value that can be extracted from the data. I've been working around data for 20+ years and not once seen a dataset where tables could be blindly joined without some conditions. In these situations it can be better to just pre-join them so the business logic is captured. You want to be pragmatic and have a healthy and constructive mix.
  - HelloNurse 1y ago
    Since I "want to be pragmatic" I want my data not repeated, so that it cannot be inconsistent, and not NULL, to simplify logic. And of course any interesting join condition involves fields from different tables.
bwfan123 1y ago
I have implemented database schemas, (without knowing database theory), and these principles are a revelation to me. A question I have is: Given a schema, are there automated verifiers for validating that it adheres to these principles ? A schema "linter" of sorts. There seem to be parallels to linear algebra here (orthogonal bases, decompositions, etc)

gitroom 1y ago

[dead]

pretoriusdre 1y ago

I really don't like using natural keys as primary keys. Natural keys sometimes need to change for unforeseen reasons, such as identity theft, and this is really tricky to manage if those keys are cascaded into many tables as foreign keys. Natural keys are often not unique either. Using the national ID example, there are millions of duplicate SSNs issued within USA. https://www.computerworld.com/article/1687803/not-so-unique.html https://www.computerworld.com/article/1687803/not-so-unique.... So, don't use natural keys as primary keys. Put them in as surrogate keys, ideally with a unique constraint.

jiggawatts 1y ago

The "natural ID" for people design reminds me of a story from a state department of education: They had two students, both named John Smith Jr. They were identical twins and attending the same class. They had the same birth date, school, parents, phone number, street address, first name, last name, school, teachers, everything... The story was that their dad was John Smith Sr in a long line of John Smiths going back a dozen generations. It was "a thing" for the family line, and there was no way he was going to break centuries of tradition just because he happened to have twins. Note: In very junior grades the kids aren't expected to memorise and use a student ID because they haven't (officially) learned to read and write yet! (I didn't use one until University.)

Pamar 1y ago
Same first name? For twins? I find it very difficult to believe this is not prevented by some law.
- jiggawatts 1y ago
  Me too, but it seems to be an oddly common occurrence: https://www.google.com/search?q=identical+twins+with+the+same+name https://www.google.com/search?q=identical+twins+with+the+sam...

jandrewrogers 1y ago

This takes an overly simple view of what domains can look like. There are data models that necessarily violate these principles, and they aren’t all that rare. Some examples: > A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity In some domains there is no natural key because the identity is literally an inference problem and relations are probabilistic. The objective of the data model is to aggregate enough records to discover and attribute natural keys with some level of confidence. A common class of data models with this property are entity resolution data models. > All information in the database is represented explicitly and in exactly one way Some data models have famously dual natures. Cartographic data models, for example, must be represented both as a graph models (for routing and reachability relationships) and as geometric models (for spatial relationships). The “one true representation” has been a perennial argument in mapping for my entire life and both sides are demonstrably correct. > Every base relation should be in its highest normal form (3, 5 or 6th normal form). This is one of those things that sounds attractive because it ignores that it requires no ambiguities about domain boundaries or semantics, which doesn’t exist in practice. I bought into this idea too when I was a young and naive data modeler. Trying to tamp out these ambiguities adds an unbounded number of data model epicycles that add a lot of complexity and performance loss. At some point, strict normalization is not worth the cost in several aspects. In almost all cases, it is far more important that the data model be efficient to work with than it be the abstract platonic ideal of a domain model. All of these principles have to work on real hardware in real operational environments with all of the messy limitations that implies.

Akronymus 1y ago
I strive to keep most things in at least the 3rd NF. Except stuff like addresses or names, those I intentionally don't push to be normalized, as they make up a single datum anyways, and normalizing creates more problems than it solves IME.
- friendzis 1y ago
  Addresses and names are nice, well-known examples for cross-domain data. It's not that attempts at normalizing these structural datums create problems per se, but rather there is no single true normalization, therefore wrong normalizations start causing problems.

peanut-walrus 1y ago

No. Real life rarely has natural keys that are unique and do not change. For example the national id number in several countries can change in some circumstances...and that is already a synthetic key.

stevoski 1y ago

Bad luck if you don’t yet have (or know) your national ID. National id is not something issued at birth in the country I live in. It’s something applied for at a certain age.

sitharus 1y ago
Where I live there’s no such thing as national ID. There’s a few documents that can be used as such depending on the purpose, and some of those change the number on every update! Never trust something outside your system to be stable.

cess11 1y ago

"Databases are representations of reality" "tell the truth that is out there" Both truth and representation are very slippery, many-faceted concepts, encumbered with millennia of use and philosophy. Using them in this way is deceptive to the junior and useless to the senior.

msla 1y ago

The example works if and only if there's one National ID per person. That's not true for SSNs. It's not true in that it is false. My statement that it is false is, in point of fact, true, and therefore not up for debate. The government even acknowledges this: https://www.ssa.gov/OP_Home/handbook/handbook.14/handbook-1401.html https://www.ssa.gov/OP_Home/handbook/handbook.14/handbook-14... > 1401.7 Can a person have more than one SSN? > Most persons have only one SSN. In certain limited situations, SSA can assign you a new number. If you receive a new SSN, you should use the new number. However, your old and new number will remain linked in our records to ensure that your earnings are credited properly. This could affect your benefits. Maybe there are countries where it is the case that nobody ever gets multiple National IDs. Maybe there are countries without fraud and where everyone can and will update their records when the government does. Maybe there is a veritable Utopia on Earth, a Cockaigne of validated data and reasonable deadlines.

deleted 1y ago

[deleted]

khana 1y ago

[dead]

photios 1y ago

"Databases are representations of reality" The national ID example is funny. Let me give you a dose of reality concerning national IDs. - I've seen cases with my country's national ID numbers containing duplicates due to human error. - National ID's for people can change. In my country, children being adopted, get a different ID after adoption. - There exist people without a national ID. Or people that don't want you to know their national ID.

HelloNurse 1y ago
Don't forget general patterns, e.g. you positively need to enter a record right now, but they didn't tell you what "key" to use; or they misspelled the "key" and you have to update it by user request.

traches 1y ago

Ohhhhh absolutely not, thank you. I want my IDs to have absolutely no meaning whatsoever.

deleted 1y ago

[deleted]

RedShift1 1y ago

Article sounds like it was written from a purely theoretical perspective and not from what happens and the requirements in real life.

exabrial 1y ago

I’m going to have to disagree. What happened what national ids are expanded to include letters? Or extra digits? I’ve lived through this mess a few times in the migrations are painful and take a ton of time away from solving actual business problems. For example: us telephone numbers. The exchange (middle 3 digits), used to physically Geolocate a consumer, because everybody had land lines. I worked on a project and an investment firm that was using it as a primary key to identify a users’s location. Holy old macro was that an unbelievably expensive and painful migration. Don’t do this. Instead, follow this principle: never ever use an externally assigned identifier as a primary key in your database. Instead: link tables with simple integers. Do not use these integers for any other purpose and assume unordered. Never let these integers leave the app. When exchanging information between systems or third parties generate a suitable external identifier with a alphabetical prefix.

al2o3cr 1y ago

Seems like a terrible idea, TBH - the reality is that values like "national ID" can be sensitive themselves. In the US, such a scheme would make even HTTP ACCESS LOGS into personally-identifiable-information requiring special handling.

datadrivenangel 1y ago

Principle of Full Normalization is a useful guide that should not be religiously followed. Mostly full normalization is good, but some objects like names and address resist normalization and thus can introduce significantly more complexity than they save by normalizing.

mannyv 1y ago

All of these "rules" will change once they hit the reality of utilization. It reminds me of this: "We need to normalize the database for better performance." "We need to denormalize the database for better performance."

QuiCasseRien 1y ago

> A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity — not by arbitrary or surrogate values. That's a very nice example of Theory VS Practice ! I agree, in Theory, PK should be identified by the natural unique key identifier of the domain. Your example with national_id is proper. In practice : - due to their varying underlying type, manipulating natural key identifier throught an application (passing values) is not always easy. UUID or INT are reliable and always works. - sometimes, we want a "poor" anonymization when passing and calculting data. As the PK is the identifier of the tuple, having a personal data for PK can be problematic. - last but not least : using PK that is subject to change is a terrible idea. An address mail, for example, anybody can be subject to change. When it's the PK, you are fuck to do or propagate the change. And in this age of gods war, Practice always win.

b-man 1y ago

Lots of peole got hung up on the example, which I thought would be be helpful on the discussion, but certainly should not replace the main point, which is: Relations, attributes and tuples are logical. A PK is a combination of one or more attributes representing a name that uniquely identifies tuples and, thus, is logical too[2], while performance is determined exclusively at the physical level, by implementation. So generating SKs for performance reasons (see, for example, Natural versus Surrogate Keys: Performance and Usability, Performance of Surrogate Key vs Composite Keys) is logical-physical confusion (LPC)[3]. Performance can be considered in PK choice only when there is no logical reason for choosing one key over another. https://www.dbdebunk.com/2018/04/a-new-understanding-of-keys-part-3.html https://www.dbdebunk.com/2018/04/a-new-understanding-of-keys...

idoubtit 1y ago

Just a side note about the historical anecdote at the bottom of the post, which is related to Notre-Dame de Paris: > 28 statues that portrayed the biblical Kings of Judah. [...] They didn’t portray French kings That's wrong. Several texts from the revolution and before still exist that prove that these kings were identified as both Judea kings and France kings. For instance, David was Pépin le bref. On one of the gates of the cathedral, the List of the French kings was engraved, starting with Clovis. That glorification of the monarchy, with parallels to the bible, was common at the time: other French medieval cathedrals show the same analogies.

b-man 1y ago
Thanks, I didn't knew that. Can you link to one such text?

thuanao 1y ago

> When a collection of such propositions is stored in a computer system, we call it a database. Is it? A database is a place data is stored and retrieved. It is literally a data base. No more, no less. Whether it logically models a domain may be completely irrelevant to store and retrieve data. I would argue that a database should not logically model a domain. Why? Because every database must store and retrieve data. Therefore data should be modeled to store and retrieve data as efficiently as possible. And the structure that most efficiently stores and retrieves data most likely does not logically model a domain.