Data Modeling – How to Model Multiple Last Names

data modeling

In Spanish-speaking countries we use more than one last name, like:

First name ↘ ↙ Last name
Pedro Arturo Rodríguez Loyola
Middle name ↗ ↖ (?)

I'm trying to model data for patient name. In our country it's important, so I can't neglect the second last name, but I would like to build an application that can makes sense to other developers, especially from English-speaking countries.

I created a similar question on English.SE, it has some insights about why it's important to persist both values.

Best Answer

Q: How does a DBA count?

A: 0, 1, many

An individual has 1 or more given names and 1 or more family names, and possibly a title. These names have an order to them. It is up to the localization and culture to determine how to refer to an individual.

ContactId
NamePart {"John", "Smith", ... }
NameType {title, given, family, ???}
Order {1, 2, 3, ... }

For Pedro Arturo Rodríguez Loyola (contact #1), you would have four rows:

1 / Pedro     / given  / 1
1 / Arturo    / given  / 2
1 / Rodríguez / family / 3
1 / Loyola    / family / 4

This way it is not limited to any given structure yet still makes sense for a given contact on there. What do you do when you have someone with 3 or 4 given or family names? or a maiden name?

Note that I've changed the order from a from previous revision of this answer - the order is an order over the entire name rather than just an order within the name type because in some cultures, the family name comes first, you may have split title parts "Sir John Smith II".

Additional Reading

Related Solutions

Database Design – Data Model for Selling Groups of Products as One Product

In the past, what I've seen at companies that do this is one of two approaches:

A "bundle" is a separate product. It's put into the database as its own product, stored in inventory as its own product, and gets removed from inventory and shipped as its own product. No refactoring required, but ties up product in inventory that could be sold individually, if it was broken out. It has the virtue of being customizable as a bundle; i.e. the cost can be different than the individual products.
A "bundle" is a "Bill of Materials," culminating in a finished product. Requires a completely different approach, and much refactoring, but preserves the integrity of individual products on shelves. There would be lots of duplication, since each individual product still needs its own bill of materials containing one item (itself).

If I were doing it, I would find a way to sell the idea of bundles as "quick-entries." It works sort of like a GUI macro; you select a bundle from the list of products, and it automatically adds the necessary individual line items (and quantities) to the invoice. You still need a "bill of materials" to implement it but you don't have to refactor the entire system.

RESTful API/Data Modeling – How to model an subordinate resource that will only ever have one element

The POST /documents/<documentId>/revisions endpoint has to also modify the DocumentDrafts table to reflect the fact that the document is now in a "clean" state and can be modified by another user

Different layer. It is important to understand that it is usually not desirable to tightly couple the API layer to the service and/or data layers.

The PUT /documents/<documentId>/draft endpoint breaks a few rules of REST including the use of the singular word "draft" (because there can only be one draft of a document)

There is no such rule. For starters, REST says exactly nothing about what URIs should look like. Beyond that, it is generally accepted in web API design that singular words are reasonable when only a single instance of the resource exists.

In your shoes, I'd use these endpoints:

/documents
/documents/{id}
/documents/{id}/draft
/revisions
/revisions/{id}

A user can GET a document. This will contain a link or a subresource with the most recent revision. To start editing, they POST to draft. This will either return 201 Created (they are now editing) or 409 Conflict (somebody else is already editing). The user who is editing will PUT to draft. To cancel editing without saving, the user can DELETE draft. To commit the draft, they POST to /revisions. A side effect of this will be to DELETE the draft. Alternately, you can require a manual DELETE of draft by the client to terminate the edit.

Now the only problem you have is what happens when the user starts an edit and goes on vacation without cancelling or committing it. :-)

Best Answer

Additional Reading

Related Solutions

Database Design – Data Model for Selling Groups of Products as One Product

RESTful API/Data Modeling – How to model an subordinate resource that will only ever have one element

Related Topic