8 ms·
Most of this advice is stuff they tell you in school that doesn’t matter at all in reality. Real advice: - Normalize until it hurts, denormalize until it work
by earthboundkid 7y ago
Most of this advice is stuff they tell you in school that doesn’t matter at all in reality.
Real advice:
- Normalize until it hurts, denormalize until it works. (I.e. prefer normal forms but denormal will have better performance.)
- You need nullable dates and nullable numbers. You never need a nullable string. If you must distinguish blank string from unset, add a column called “unset”. Using null for this will just result in bad data entry.
- Names should be stored as display_name, first_name, and last_name. A display name is not the same as just first plus last. They have different purposes, so don’t shove them together.
- When in doubt, just have a column full of JSON and figure it out later.
- Offset pagination is bad for performance. Avoid it at all costs unless you want some spider indexing page 10,432 of your archives to eat all your CPU.
- teddyh 7y ago> - Names should be stored as display_name, first_name, and last_name. No. “[…] refer people to this post the next time they suggest a genius idea like a database table with a first_name and last_name column.” — https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-... You could, conceivably, have a "display_name" column, an "informal_name" column, a "formal_name" column, etc. depending on your needs. But for absolutely most people and purposes, a single "name" column will suffice, and will steer programmers away from making unfounded assumptions about people’s names.
- NOGDP 7y agoFrom the article: > This list is by no means exhaustive. If you need examples of real names which disprove any of the above commonly held misconceptions, I will happily introduce you to several. ... > 40. People have names.
- jayd16 7y agoSome oauth systems will give you a user id or an email but not permission to see a name, for example.
- zimpenfish 7y agoPrince, famously, didn't have what we'd recognise as a name for years. All the "Unknown Soldier"s don't have names. "John/Jane Doe" doesn't technically have a name.
- goto11 7y agoThese examples seem somewhat contrived, but of course it comes down to the use case of the database. I'm sure Prince remained registered under his legal name on contracts, checks etc, even if he used the symbol in marketing. But a database over bodies in a morgue obviously need to be able to support unidentified bodies.
- MaxBarraclough 7y agoInteresting, I think you're right. According to Wikipedia, he only changed his stage name, not his legal name. The BBC didn't seem to make the distinction. https://en.wikipedia.org/wiki/Prince_(musician) https://en.wikipedia.org/wiki/Prince_(musician) , https://www.bbc.co.uk/news/magazine-36107590 https://www.bbc.co.uk/news/magazine-36107590
- humanrebar 7y agoNewborns have no known names until the birth certificate is filled out. There is still data about their medical care that needs stored in a database.
- tasogare 7y agoAlso for international users browsing in English labeling the fields "given name" and "family name" are way more clearer than using first and last names.
- rumanator 7y agoAlso, in some cultures like in Spain the legal surname is typically the first family name, which might be the first of two or three family names.
- pezezin 7y agoWhat do you mean "the legal surname"? I'm Spanish and my legal name is my full name with both family names. You can't just ignore one. Also, Spanish people have two family names, but they can be composite, so they may look as three or four family names. However, most people are usually known by one family name, but which one is used depends on which one is more easily recognizable.
- rumanator 7y ago> What do you mean "the legal surname"? I'm Spanish and my legal name is my full name with both family names. I'm referring to the primer apellido. Even government forms require that people break down their surnames in first surname and all trailing surnames.
- Wildgoose 7y agoExactly what I was thinking - plus a field to state the order they should be displayed in seeing as Asian cultures usually state the Family Name first.
- pezezin 7y agoShouldn't that be a localization/translation issue? My experience living in Japan is that they will write family name first if using Japanese, but given name first if using an European language.
- codr7 7y agoI spent 13 years maintaining a large-ish system that separated first- and last names for customers. Separating makes searching/indexing on last name easier. The alternative would be to put last names first by convention, which renders them unusable for anything else; or go insane trying to find a universal way of splitting and recombining for display. Keeping them separate inevitably results in a convention to put company names in the last name field for corporations, and failed searches when the convention is broken. It's one of those questions that I suspect will keep bugging me until I die.
- henrikschroder 7y ago> Separating makes searching/indexing on last name easier. What do you mean "last name"? If you have two last names, then what? If you have no last name, then what? > Keeping them separate inevitably results in a convention to put company names in the last name field for corporations If you only have a single field for name, this problem disappears.
- codr7 7y agoI mean everything but the first name. Yes, but then you have to deal with the indexing/search problem instead.
- henrikschroder 7y agoYeah, but if you only have one last name field, you can only index on people's first last name, not their second last name? How are you going to solve that?
- naasking 7y ago> Separating makes searching/indexing on last name easier. The point is that such a search or index is a mistake. It's simply not meaningful based on the real properties of names, it just happens to work 99% of the time if you're only dealing with a mostly homogenous culture.
- 7y ago
- Mikhail_Edoshin 7y agoYou'll regret this decision as soon as you have to integrate with another system that uses separate first and last names. Or as soon as you want to build a sorted list of five thousand names or output a bibliography in a specific format or anything. Technically the right approach to the problem of storing international names would be to store markup: a string with an associated structure and a discriminant: <name lang="en"> <first>Kenneth</first> <middle>Elton</middle> <last>Kesey</last> </name> <name lang="ru"> <given>Fyodor</given> <patronymic>Mikhailovich</patronymic> <surname>Dostoyevsky</surname> </name> <name lang="es"> <given>Gabriel</given> <family-paternal>García</family> <family-maternal>Márquez</family> </name> You can then have precise automated ways to transform this into whatever derived form you need. Using a single name field is just avoiding the problem. Using multiple fields is a maintenance burden, because in most cases fields are not fully independent and you have to update them all to keep the data coherent.
- goto11 7y agoUsing markup does not really change anything, that is just a way of serializing a data structure. You can represent the same information in normalized form.
- Mikhail_Edoshin 7y agoOf course, I don't mean storing literal XML, but I think it's important to support generic markup in databases. It's not that hard, after all; something like these five tables: Markup (markupId, string) Ns (nsId, uri) QName (qNameId, nsId, localName) Elt (markupId, eltId, qNameId, start, length /* string indices */, level, index /* or other way to represent hierarchy */ ) Attr (eltId, qNameId, value) would be enough to store and fully index any XML out there. I think markup is an important separate data type. We have records, we have scalars, now we have a hybrid type, markup: a string (or maybe a byte/bit stream) + records linked to indexes in this string/stream and maybe forming a hierarchy.
- eecc 7y ago
- MaxBarraclough 7y agoBut then you don't have my informal name. When I receive an informal update email, it would be clunky to have it begin Dear Max Barraclough. You can't automatically deduce that the way to address me informally is Max, without making assumptions that will break outside the English speaking world. See Points 18, 24, and 30. The W3C suggest that one way to handle names is to have a Full name field and a What should we call you? field. https://www.w3.org/International/questions/qa-personal-names https://www.w3.org/International/questions/qa-personal-names
- marcosdumay 7y agoIf the database owner is not billing you, he probably doesn't need your formal name, so he can have just one column, and call it "name". Otherwise, yes, he'll need the 2 columns.
- ddrt 7y agoHow do you store addresses? There’s a use case for breaking things up.
- henrikschroder 7y agoIf I had a dime for address forms that have "state" as a mandatory field, while having a country dropdown that contains countries that do not have states, I'd be rich, RICH I TELL YOU!
- retreatguru 7y ago> “ Offset pagination is bad for performance. Avoid it at all costs unless you want some spider indexing page 10,432 of your archives to eat all your CPU.” I love this! It’s so true only bots dive that deep and it’s usually a total waste. But what do you recommend instead? Should you just deny any calls past page 10? Or do away with pagination altogether?
- hombre_fatal 7y agoIf there's somehow no need to show anything past page 10, then by all means, just do that. But most applications can't get away with that. There are plenty of resources for pagination online. For example, your [Next Page] button could use a `where date > {date of last item on prev page}` since you have an index on date. e.g. <a href="/things?after={prevDate}">Next</a>. A forum topic could have a consecutive and contiguous "index" on each of its posts that starts at 0 and increments. Now going to page 1000 is simply a matter of grabbing posts between index 990 to 1000 which is instant.
- emmelaich 7y agohttps://use-the-index-luke.com/no-offset https://use-the-index-luke.com/no-offset
- samanator 7y agoThanks! This is really useful! I didn't know that limit offset scanned all rows before the offset.
- hilbertseries 7y agoDon’t use offset for paging. Presumably you’re sorting the results by something, add a condition on that value in the where clause based on the last row in the previous page.
- janpot 7y agoSuper fun until you have a column that has an amount of duplicates greater than the page size. Now you'll have to add a second column to your order clause, your serialized cursor and your where clause. Oh, and don't forget to check whether your database driver uses the same date precision as your database does, because you might be in for another surprise.
- nerdponx 7y agoYou never need a nullable string. If you must distinguish blank string from unset, add a column called “unset”. Using null for this will just result in bad data entry. What's an example of a data entry error that results from this scenario? Wouldn't it entirely depend on the data entry interface?
- earthboundkid 7y agoSomeone is filling out the form. They skip MI. Do I mark it as null or blank? The only way to trust this is to have a big check mark that say “does not have a middle initial” that stores a boolean. But realistically, who cares? Unless you’re dealing with legal names, you don’t need to distinguish the cases.
- nerdponx 7y agoOK, but why does that require the database to have non-nullable string types?
- earthboundkid 7y agoNo reason per se, but it’s inconvenient and I’ve seen tons of bugs from “NoneType has no method ‘uppercase()’” that this eliminates.
- nerdponx 7y agoSorry, I can't get behind changing the database design to accommodate under-specified data contracts and/or lazy software development.
- earthboundkid 7y agoI can't get behind letting theoretical database design concerns trump real world problems with unexpected nulls leading to 500s in production. :-)
- goto11 7y ago> denormal will have better performance No it wont! In general it will have worse performance. Deliberate denormalization can be used to optimize for particular queries but it will make other operations more expensive and/or error prone. So it has to be done very carefully and with a full understanding of cost vs benefit. And many database systems supports materialized queries which give the same optimization without having to compromise the schema.
- loopbit 7y agoHave you ever used a complex database in a formal, by-the-book, third normal form? The number of tables explodes, the number of joins become a pain and every single data change requires careful planning. As for the "many database systems support materialized queries", I only know of a few that truly do: Oracle and DB2. PostsgreSQL supports them, but you need to handle updating them yourself. SQL server kind of supports them, but are limited. In other RDBMs you can work around that using triggers (or similar concepts), but that IMHO is definitely a bad practice and the times I've used that (usually because there was no other option) has only brought me grief.
- mhd 7y agoI think there's a decent amount of hyperbole, but even taken at face value: Every single data change should require careful planning. Especially if it's a schema change, and not just a query. You can do a lot with generated code, refactoring, etc. Data is much less agile, so I'm a lot more conservative when it comes to that end. Never mind that with decent naming, table comments etc. it's a good documentation in itself.
- goto11 7y agoDenormalization means you (by definition) have duplicate and redundant data in the tables. This certainly requires careful planning around updates to avoid data corruption. > The number of tables explodes This is IMHO a backwards way to understand normalization. Normalization tell you how many "logical relations" you have in the data - whether you like it or not. If you decide to squash multiple logical relations into the same physical table then you get redundant data (by definition). This might be a fine optimization, but it doesn't reduce the inherent complexity of the data model. It just saves some joins as the cost of more complex update logic.
- goto11 7y ago> Names should be stored as display_name, first_name, and last_name. A display name is not the same as just first plus last. They have different purposes, so don’t shove them together. It all comes down to the purpose of the database. There is no one-size-fits-all solution. For a hospital which need to register newborns, you can't even count on everyone having a name. For a library database, you need both first name and last name of authors, since books are sorted by authors last name - but having a "display_name" would be redundant and violate normalization. If you can get away with having a single "name" field, this will say you lots of trouble. Only add additional fields if you need it for some use case.
- Cthulhu_ 7y agoThat assumes people who read it actually have been in school; there are a LOT that never had anything about database design. Your other points make a lot of assumptions and there's a lot of footguns in them but other commenters have pointed them out already.
- throwaway_pdp09 7y agoFuck me, are you trolling? Guy with actual DB experience here, so: > Normalize until it hurts, denormalize until it works. (I.e. prefer normal forms but denormal will have better performance.) No it won't, it depends entirely on the situation. Denormalised data (besides allowing update discrepancies) can expand the amount of data, thus push otherwise memory resident working sets onto disk. Big slowdown. You are wrong. The correct answer is "it depends" - and denormalising really is a last resort that has to be done judiciously and with care. > You need nullable dates and nullable numbers. You never need a nullable string. Utter unjustified garbage. Correct answer (from a practical, not super-normalised form) is: "it depends" > When in doubt, just have a column full of JSON and figure it out later. OMG please never let me come near any fool who does this. I was called in to consult on a database problem and they did the same but with XML. It was really bad, and bad for performance and comprehension. Please nobody listen to such 'advice'.
- TomMarius 7y agoIt must be said that json performance of postgres is good.
- holoduke 7y agoI believe the right answer is somewhere in the middle. I know a couple of successful products where the dB scheme is completely abused. Yet commercially a succes for over 8 years. A good dB specialist also knows the implications of a 'bad' design choice. It's all about the impact at a later moment. Just like software design in general. Finding the balance between complexity and simplicity. Between slow and elegant versus fast and ugly. Balance is key
- throwaway_pdp09 7y agoI did say clearly it depends. And that's because it does. > Between slow and elegant versus fast and ugly No! Unqualified, this is a false dichotomy! Because often you can have elegant and fast, and that's because elegance of design often gives you that speed. Not universally true, but much more often than not.
- Aeolun 7y agoYour main database should always be 100% normalized. You can add a separate database for lookups later that can be as denormalized as you want.