I 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.
Yeah, 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?
> 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.
> Keeping them separate inevitably results in a convention to put company names in the last name field for corporations
In most (IMO sane) designs, a company name would usually be a separate field (or better yet: companies would be represented in an entirely different table).
But in many cases the customer is either an individual or a corporation. Keeping them in separate tables turns simple tasks such as generating a list of customers into joins. Which brings us to normalization, too much of that kills performance.
I feel like if a join is killing performance, either it's a really weird join (e.g. joining on an OR) or you ain't indexing.
Regardless, you could always just assume everyone's a "corporation" (and for individual customers, that "corporation" would just be a reflection of the individual customer's data). That, or only deal with contacts within a "corporation" (and an individual customer would just be a contact with a null "corporation"). I've seen both approaches in practice, and while they have their tradeoffs, they work reasonably well.
"Corporation" is a pretty wonky term for this, anyway (not all non-individual customers are corporations); "organization" would be better. But I digress.
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.