You Win, Broken Database Schemas | Hacker News Reader

You Win, Broken Database Schemas

christine.website

43 pointsggoo69 comments

What's the proper solution here? Just a "name" field? Maybe formal_name and informal_name so you can do things like "Howdy ${informal_name}" without awkwardly using a last name?

meta: What is the purpose of the M01 10 2022 date format? Is that intended to reduce ambiguity between the month and day? Why not an ISO 8601? Why isn't it M01 D10 Y2022?

Author here, yes it's intended to reduce month/day ambiguity.

Cool, thanks. It worked but I have never seen the format before. Any reason for not using a standard format? Just creative license?

Creative license mostly. No real reason otherwise!

I’m using yyyy-mm-dd for pretty much everything, since it also conveniently sorts by time when used in file names and other alphabetic sorting systems :-)

I think full_name or just name would be sufficient. And optionally allow given_name, middle_name, family_name if you really need it/want it for some purpose.

But for a lot of common use cases, there's really no need to separate first and last name in a database imo.

These names follow with schema.org attributes. https://schema.org/givenName ( first ) https://schema.org/additionalName ( middle or alias ) https://schema.org/familyName ( last )

I’ve had to have a name field and a sortable name field before:

Name: The Who, Sort_Name: Who

Just use a 'name' field if possible, yes.

Otherwise, add a whole bundle of fields and make none of them mandatory. That's what Google Contacts does, and that seems to work ok.

Contacts is an interesting use case. I prefer to have my parents full names in the contact but also have a display name set so texts from my mom say "Mom". So even in the case of "traditional" names there's still a use case for more than just first and last.

There isn't a "proper" solution that handles everything because just when you have text nailed down, someone decides their name is a novel pictogram (Prince) or an odor (no one yet, but if the iSmell was still being manufactured...).

It is an instance of a general problem that policy can be anything, even stuff that is inconsistent. Closest I've ever got was in an object DB where I could define various subclasses of a Name class that were related to the Person instances (along with titles and whatnot). There were obvious efficiency and UI issues. Harder to do with relational.

As far as I know, almost all practical cases can be dealt with by storing family name (which must be optional), given name, and locale. Or just store a name as arbitrary Unicode text, if you don’t need to do anything fancy.

Nickname is another related mechanism that allows for a single name field.

> meta: What is the purpose of the M01 10 2022 date format? Is that intended to reduce ambiguity between the month and day? Why not an ISO 8601? Why isn't it M01 D10 Y2022?

I've never seen that before, it's hilarious. As to why, imagine you're from a country that uses a subpar date formatting like MM DD YYYY, and while you're conscious enough to notice its badness and improve on it slightly by adding an "M", you're not conscious enough to search for a fundamentally different approach - a sane date format. A local maxima of sorts.

The dark truth is that it was inspired from the iOS Lojban locale date format. I'm not sure where they got that from, Lojban doesn't define a date format.

My name is the SHA1 hash of the UTF-8 representation of the next 30 unicode code points that follow it.

If you don't have 30 more code points worth of stuff to say, you can feed fewer bytes into the hash. But then you can never speak about me again.

What's the database schema for that?

How do you respond without knowing the following content?

Drop in a future/promise and backfill. Or just generate 30 nulls.

I think the proper solution is probably going to vary a lot depending on use case. Just full_name is probably a good default.

Reconsidering whether you actually need to store a name, or whether you need more than an “informal” name is another good default. I’ve scare quoted “informal” because what you probably mean is somewhere between “preferred conversational” and “legally identifying”, and people may prefer to use a name they’ve chosen in formal settings (my understanding of Xe’s article suggests xer do [and sorry if I’m misrepresenting that, or misusing xer]). I have used a chosen name (originally chosen by a friend) for almost 20 years, both in formal and informal contexts. And many many people do, even from childhood (eg choosing to go by a middle name or a specific variant of the name on, say, their birth certificate).

If for whatever reason you need or have a good reason to ask for/store a “formal” name and a “preferred conversational” name, those are probably reasonable default names, though you may want to also allow for multiple “conversational” names, and without qualification that can get ambiguous very quickly (which loops back to “reconsider” IMO).

Ask rather than trying to be 'smart' and out-think the end user.

Most of the other replys talk about 'Legal' names vs various levels of friendly name.

How about Mailing Address fields for snail mail? Don't decompose it, just have a full text area and store EXACTLY what the user provides. POSSIBLY toss it at a an address validation routine your postal services. Be prepared to handle an 'International' printed label where you might print a blob of text you don't understand, targeted to a country you do, and require manual processing for country, payment, and customs forms validations outside of the typical flow.

Try to show the end user examples of where and how the information will be typically used.

Never, ever, break down components of user provided input; at least not without letting them confirm if they're correct and allowing them to skip it if it isn't.

Anyone who's had to parse free-text international addresses can tell you that this is terrible advice. People type things into computers incorrectly all day long. We prompt users to select things like country from dropdowns with good reason. Imagine having to delete user data for GDPR compliance and needing to determine which of your 1,000,000 users are in the EU from a free-text field. Oh, this user wrote "Czechia" instead of "Czech Republic"? What do you mean they wrote the name with accents? Oh they wrote the name in their native language and not English? Have fun parsing all this:

https://en.wikipedia.org/wiki/List_of_country_names_in_vario...

The parent did say “targeted to a country you do [understand]”. The impression I get is that for most address handling the only thing that’s critical to have structured is the country; probably everything else should be free-text until you have a reason not to[1], at which point you should find someone who deals with them for a living (probably a third-party provider), and even then it's probably best to provide a fallback for the weird edge cases.

[1]: One good reason is that, as you say, people get things wrong—even their own address—on a regular basis, and you may be charged more money if you’re e.g. shipping things with the wrong postal code for the rest of the address. But even then it’s good to provide a workaround if necessary; post office databases can be wrong too!

> Anyone who claims to have their identity figured out is either in denial or stopped digging into it for the time being. The rabbit hole truly never ends.

I agree with that part, but I also think that often giving a name isn't really about identity, but about having a shared space to communicate. For example, when I want to order something online, I have to give a delivery address. I may live here. I may not. It might be just a PO box, or the address of a friend. But, by giving this address, I'm saying "put what I want in a box, I'll handle it from here". I feel the same for names. It's not about expressing who I am deeply, it's about giving something to people that they can use.

Now, as usual, the correct answer here might be to not even ask for a name in the first place. Usually websites have my email address, and that's enough. They can put the information that they want to transmit to me in a box, and I'll take a look.

> > Anyone who claims to have their identity figured out is either in denial or stopped digging into it for the time being. The rabbit hole truly never ends.

Like you stated, it's a bit of a leap to conflate self identity and one's name. It's just an identifier, not the full self.

And the false dichotomy seems a tad presumptuous.

Some folks truly do have a firm understanding of themselves and lack self-doubt.

Or for some their identity isn't the focus of their activities, and acts as a simple tool to achieve other things.

Or perhaps what they've figured out is to accept that their identity is fluid and will forever be hard to define.

Or...

I'm not sure the name/address analogy fits. Names are deeply personal, at least for some people. An address is not open to interpretation and doesn't convey any personal meaning.

On the other hand, if the pizza place asks for a "home" address that makes me uncomfortable (conceptually offensive/technically incorrect) when I order to a friends house. I know what it means so I fill in the correct value to get the result I want but the form should say "delivery address".

> An address is not open to interpretation and doesn't convey any personal meaning.

I've heard plenty of time to not put my address in my CV if I'm not currently living in a nice place. Especially for students looking for job, a foreign-sounding name and an address in a relatively bad neighborhood (where prices are cheaper) can be enough for people to reject your application.

> On the other hand, if the pizza place asks for a "home" address that makes me uncomfortable (conceptually offensive/technically incorrect) when I order to a friends house. I know what it means so I fill in the correct value to get the result I want but the form should say "delivery address".

I think we agree in that forms shouldn't ask for a "personal" information when they need a "practical" informations ("where do you live?" vs "where should we deliver your pizza?"). Names are like that, I think. 95% of the time, when websites ask for a name, they want something to put in marketing emails or when you're connected. I can understand that most people don't mind, but adding an option to opt out shouldn't be too hard.

Software makes processes more efficient. But it does so at the cost of hardcoding assumptions. These days, effecting social change requires not just convincing people (already quite a tall order), but also changing the software that automatically encodes the assumptions made under the previous societal context. As an example, consider abolishing daylight savings time: nowadays you not only have to convince people that it's a good idea, but you also either need to convince them that all the broken software that assumes DST is either worth fixing or worth putting up with forever. Or consider spelling: in the past spelling reform was a thing that actually ever happened, but these days any attempt at spelling reform would be crushed by the inertia of automatic spellcheck (on the flip side, it might be easier to convince Google and Apple to change their spellcheck dictionaries than to convince people to change their spelling on their own); sadly, English will be stuck with the tragedy of "ough" forever.

Sadly, it isn't always up to the people who design the schemas.

For example, Iceland has very restrictive naming laws:

https://en.wikipedia.org/wiki/Naming_law

> Parents are limited to choosing children's names from the Personal Names Register, which as of 2013 approved 1800 names for each gender.[20] Since 2019 given names are no longer restricted by gender.[21] The Icelandic Naming Committee maintains the list and hears requests for exceptions.

Israel apparently demands first and last names:

> According to a law from 1956, a person should have a first name (more than one is permitted) and surname (a double-barrelled name is permitted).

Finland is, apparently, worse than Iceland:

> The Names Act of 1985 requires that all Finnish citizens and residents have at least one and at the most four first names. Persons who do not have a first name are obligated to adopt one when they are entered into the Finnish national population database. Parents of new-born children must name their child and inform the population registry within two months of the child's birth. The name may be chosen freely, but it must not be

> a name used primarily by persons of the other sex

> a name foreign to the naming tradition in Finland

> a surname, except a patronymic as last given name

> a name already used by a sibling, if this is to be the only given name.

So systems in these countries should just be built without any regard for foreign names?

There isn't really a working excuse for a broken schema.

Just have a blanket 'name' field.

The name someone wants to be called and their legal name isn't necessarily the same, either. If you're writing normal software, you shouldn't really care about these legal provisions. I guess you'd want a 'Billing Name' too in most cases.

> So systems in these countries should just be built without any regard for foreign names?

I never said anything about "should" and I think this conflation of "what this person knows the world is" with "what this person thinks the world should be" is detrimental to discourse. I've seen it in multiple places, especially online, and it leads to arguments where both people actually agree on what should be, but one of them is talking about what is and the other person can't or won't tell the difference between the two.

Which of the restrictions you listed would be any more difficult to comply with if your software has a single full name field?

Philosophically a database schema should reflect reality to the extent necessary to achieve your goals. In the case of a population registry database codifying the laws of the country in the database schema is a feature, not a bug. The problem in that case is the laws, not the schema.

Law is something decided by judges, possibly in Iceland too.

The database should reflect the reality that humans are messy and not try to out-think / hardcode patterns.

Why does your software need to enforce icelandic law?

It would were I writing software for the Icelandic government.

Why do you think I'd have any choice in the matter?

These laws are fascinating. I wonder, what was the motivation for creating them?

Iceland is a somewhat special case. As a tiny culture of < 400k people, it has to work hard to maintain cultural distinctiveness in an interconnected world. They don't just have a restrictive naming law, they also have dedicated teams which are focused on maintaining and preserving a distinctive definition of their language.

https://en.wikipedia.org/wiki/%C3%81rni_Magn%C3%BAsson_Insti...

> The invention of permanent, inherited patronyms was, after the administrative simplification of nature (for example, the forest) and space (for example, land tenure), the last step in establishing the necessary preconditions of modern statecraft. In almost every case it was a state project, designed to allow officials to identity, unambiguously, the majority of its citizens. When successful, it went far to create a legible people. Tax and tithe rolls, property rolls, conscription lists, censuses, and property deeds recognized in law were inconceivable without some means of fixing an individual's identity and linking him or her to a kingroup. Campaigns to assign permanent patronyms have typically taken place, as one might expect, in the context of a state's exertions to put its fiscal system on a sounder and more lucrative footing. Fearing, with good reason, that an effort to enumerate and register them could be a prelude to some new tax burden or conscription, local officials and the population at large often resisted such campaigns.

-- James C Scott, Seeing Like a State: How Certain Schemes to Improve the Human Condition Have Failed

Language is incredibly contextual. Database schemas are mostly rough approximations, generally driven by the principle “close enough to be useful” rather than “a one-to-one model of reality”.

That being said, I’ve gone though a similar caving-to-the-reality-of-database-schemas a few years ago, when I changed my music making artist name from a single word to a first name, last name schema.

A riddle that stumps 3rd graders:

Q: "What belongs to you but others use it more than you do?"

A: "Your name."

Given that your name is going to be used by other people a lot more frequently than you're going to use it yourself, it would make sense to make sure that your name is convenient for others to use.

Another thing - any person defining their own name could technically have a valid caveat that breaks some schema. I could decide that I can never have the same name after it's been uttered once. I could decide that my name is the last ten thousand numbers of pi. I could decide that my name is only allowed to be said by a person, and not rendered by a computer, because my name belongs to only the heavenly language of humans. I could decide that my name is the same as my social security number, which would make any system that doesn't have PCI-DSS control on my name out of compliance. There is no limit to the ways that a name can be restricted in a database, because there's no limit to the ways that a name can be defined. We decided collectively in the western English speaking world to assume that mononyms are more of a novelty than a real identifier.

There will be other restrictions. There always are. An example: the restriction that people using a website are literate. I think you'll find that there are far more illiterate computer users than there are people with mononyms, and yet no one designs websites to cater to those who are not able to read.

PCI: payments card industry

They don’t care about your SSN. They only care about credit card numbers (“PANs”).

Okay. Let's change the example. I make my full, legal name the same as my credit card number.

> Given that your name is going to be used by other people a lot more frequently than you're going to use it yourself, it would make sense to make sure that your name is convenient for others to use.

I’m not sure a riddle that stumps young children is a good basis for how people should define their own identity. I certainly don’t think it should defer to the convenience of others. Especially when identity, or conflict thereof, can be a source of trauma and/or expose people to serious risk.

> I’m not sure a riddle that stumps young children is a good basis for how people should define their own identity. I certainly don’t think it should defer to the convenience of others. Especially when identity, or conflict thereof, can be a source of trauma and/or expose people to serious risk.

The riddle is a reflection of reality. I used it as an example to illustrate the idea that everyone - including children - implicitly know that a name is a social contract with everyone (including software and government databases) that uses that name.

> Especially when identity, or conflict thereof, can be a source of trauma and/or expose people to serious risk.

Identity absolutely can expose people to risk. No system, anywhere, has ever exposed someone to a special form of risk or trauma by asking for a first name and a last name. They absolutely have by asking for valid government names without privacy protections (as Facebook, Google+, and others have), but that's an orthogonal issue.

> No system, anywhere, has ever exposed someone to a special form of risk or trauma by asking for a first name and a last name.

Countdown til this gets added to Falsehoods Programmers Believe About Names, if it hasn’t already.

Author here, part of the reason I want to disassociate myself with my assigned at birth last name is for trauma reasons.

Does the requirement to define any last name at all expose you to a special form of trauma that would be mitigated by not having to define any last name, even if the last name is of your choosing?

I would rather it be a last name of my choosing if I have to have one at all.

That's the point I was making. The mere existence of a constraint that says "you have to define a first name and a last name" is not a traumatic constraint. Whether the first name and last name match up with a previous name that has baggage is a separate issue from whether or not it's okay to ask for any first name and last name. A database schema does not ask you to provide a problematic last name, it asks to provide a last name. That's why I said, in the comment responding to a great-grandparent comment, "They absolutely have [exposed someone to a special form of risk or trauma] by asking for valid government names without privacy protections (as Facebook, Google+, and others have), but that's an orthogonal issue."

> That's the point I was making. The mere existence of a constraint that says "you have to define a first name and a last name" is not a traumatic constraint.

It can be, for me. It depends on my state of mind when I encounter it. Recall isn’t always traumatic for me, for which I’m grateful. Sometimes when I hear my family name or I’m compelled to say/write it, along comes a flood of other mental references. Many of which are traumatic. When I’m compelled to put name values in forms I… have trained my mind well to consider how deeply I’m going into that memory cave, but I’m already there before I’ve evaluated whether I think that form needs my information for valid reasons.

Your “point” that people can substitute names for convenience of data collection is not avoiding trauma, it’s avoiding you bothering to accommodate it.

Me faced with just the fact of the whole of my name, my family history, I’m looking at abuse and suicide and misery. Even if I ultimately write in “eat” for first name and “butts” for last.

I’m glad I still have the time window to self reply and encourage anyone still reading to register as “eat butts” everywhere you possibly can. It won’t accomplish anything important other than it’ll hopefully make you snicker, but please have a good laugh about butts and eating thereof.

I made that leap, and I’m sorry to hear that but I’m glad it wasn’t an inappropriate leap.

Your initial comments are thought-provoking, but to be blunt: the examples you give are the sort of hyper-exaggerated strawman so often deployed against people with marginalized identities, intended to induce the slippery slope fallacy rather than doing anything about actual problems.

There are plenty of names (as enumerated in the “Falsehoods programmers believe” article linked from the post) which have complications already, without needing to invent new ones.

I know the post - it includes most of the examples I gave! "I could decide that I never have the same name after it's been uttered once" is the extension of "8. People’s names change, but only at a certain enumerated set of events". "I could decide that my name is the last ten thousand numbers of pi" is an extension of "40. People have names". "I could decide that my name is only allowed to be said by a person, and not rendered by a computer, because my name belongs to only the heavenly language of humans" is the same as "11. People’s names are all mapped in Unicode code points". "I could decide that my name is the same as my social security number, which would make any system that doesn't have PCI-DSS control on my name out of compliance" is the same, in spirit, as "31. I can safely assume that this dictionary of bad words contains no people’s names in it".

I'm not inventing any new ones. "Falsehoods programmers believe about names" is a superset of the examples I gave. If you have issues with the examples I gave, then it's because you have issues with what is implied by the article, and not about my examples. Everything that I said flows directly from reading that article and understanding its implications.

It is still very much hyper-exaggerating, even if you can claim that you’re technically within the realm of plausibility.

In my opinion, your post would have been more engaging and less polarizing had you omitted that section altogether: the fantastical hypotheticals draw the reader’s attention away, and do not seem to add anything that more staid examples would not have offered while avoiding reducing the salience of your main argument.

It is not hyper-exaggerating any more than "Falsehoods programmers believe about names" is. That post lists as a counterexample the unpronounceable name that Prince went as. It described a scenario where no one has names and people only refer to each other by familial relationships. It said that it cannot be assumed that people's names can be written down. If you're willing to use "falsehoods programmers believe about names" as a guide for implementing a system for storing names, then you have to be able to handle my cases too. If I'm hyper-exaggerating, then so is that blog post. If I'm committing some kind of slippery slope fallacy, then you need to tell me how my examples are materially different from the one in the blogpost and draw a line that is able to say "this fallacy is true and we should accommodate it, but this one isn't, and we shouldn't."

But inventing a new problem is exactly what the author of this post is doing? This is not someone who is being marginalised because of their involuntary name, this is someone who is wilfully choosing to use a name that doesn't fit social expectations.

We're currently making an effort to restructure our databases so that we can accommodate mononyms. Nice to see at least one person might find that cool lol

"As with many falsehoods that programmers believe, names have surprising complexity. Although Americans often assume users will have a single “first” and “last” name separated by a space character, this is by no means true around the world. Some last names (“van Hardenberg”) include spaces, as do some first names (“Sher Minn”). Some people have a single name (“Madonna”). In Japan, family names are written first, then given names. For an in-depth treatment of how to correctly handle names, we recommend this enjoyably written W3C guide, but our brief recommendation is that you ask your users how they like to be addressed, and what to call them in a formal context." https://www.w3.org/International/questions/qa-personal-names https://www.inkandswitch.com/cambria/

We live in a society and names are tools for relating to other people with. I think it's fine to expect people to either conform to local cultural conventions or accept responsibility for any consequences themselves. I changed the name I go by when I immigrated to one that fits better with the people around me (and would have changed it more thoroughly if I'd known before the current form of it got entrenched in this country's bureaucracy). Expecting the world to bend to your arbitrary naming whims (and it is explicitly a personal whim in this case) instead feels pretty entitled.

The assumption here is that the entire world uses western naming conventions, there are many cultures where people are given one or five names or whatever number, and western society (or our idea of what a name is) is forcing them to conform.

These are not always 'arbitrary naming whims'.

> there are many cultures where people are given one or five names or whatever number, and western society (or our idea of what a name is) is forcing them to conform.

And yet somehow those aren't the people we see complaining (and I'm speaking as someone whose name currently gets mangled because I have the "wrong" number of names).

This makes me really curious about how people with legal mononyms (Ye, Cher, etc.) have their taxes filed or medical forms filled out.

On paper forms you usef to just draw a line theough the field to make clear that you didn't just forget. I've seen electronic forms that require you enter "N/A" for the same. Would be amusing if someone chamged their name to "N/A".

In some countries it's not legal. If it is legal then you complain, you muddle through, you accept compromises like an extra space in a few cases.

oh please, shouldn't it xe. website not Christine. I have 4 names and it doesn't always work out. It's not a big deal to not accommodate xe and Socrates perfectly either.

The solution really is simple: `preferred_name TEXT NOT NULL`

If it must match your given name, feel free to add `legal_name TEXT NOT NULL`, at the cost of some really awkward form filling for your users.

While on the topic of names, could we discuss the difference between given name(s) and middle names and the implications this has on anglocentric software/database design?

While middle names might be common in the anglosphere, in other countries (e.g. Germany) it is common to have multiple given names, which is not the same as having a single given (first) name followed by zero or more middle names.

Here is a simplified version of how it works in Germany:

Informally, people usually go by exactly one of the given names, but notably, this can be any one of the multiple given names, since these have equal status by law (this is different from going by your middle name, which have other legal implications, depending on the jurisdiction).

Using all given names is usually only required for official purposes (e.g. on a passport). Note that using a combination of more than one (but not all) of the given names or changing the order (without doing an official name change) is not allowed. A person can, of course, choose to use all given names in day to day life too.

This means that a person with the given names Foo Bar and the family name FizzBuzz may use any of the following names:

- Foo FizzBuzz

- Bar FizzBuzz

- Foo Bar FizzBuzz

Note that it is perfectly legal for this person to go by Bar FizzBuzz even though "Bar" is not their first name (of the given names).

Also note that changing your given names (other than their order) is not easily possible for most people (according to current law), so if you require someone to give you their full name (here Foo Bar FizzBuzz) for legal reasons, please also give the user the option to at least state a simplified version of their legal name for display purposes (such as any single one of their given names which may not be the first).

TL;DR

Do:

- Allow people to enter multiple given names, even if you also ask for middle names separately

- Consider that the same person may use a different single given name for different purposes (e.g. basic account details vs. name details in the shipping address)

Don't:

- Prompt for the full name (including all given/middle name, or the family name) unless you have to for legal reasons; if you do have to, at least let the user pick another name for display purposes, which may be derived from their full legal name in unexpected ways

- Omit any of the multiple given names because "middle names are optional" or "not part of the legal name"

- Forcibly abbreviate the remaining given names after the first given name

- Drop any part of a given or family name if the name consists of multiple components separated by a dash

Reply on news.ycombinator.com