In previous database model I've had an entity that had a simple name property. Later I'd find out that a single name was no longer sufficient - rarely entities had multiple names and it was time to rethink the model.
Now, the obvious answer would be to just add a 1:n relation to a table holding the names and ditch the name column, but I need to also keep track of a default or primary name value and I always need to have exactly one - preferably enforced by the database. So I can't just do what I described.
I also need to be able to easily search the names, so the solution to keep the name column while having the additional table hold only the "extra" names would make that slightly more inconvenient (and it just doesn't feel right).
Now there are two actual solutions I'd imagine as working and correct - we can either have an extra column in the names table that would say whether a name is a primary name (it could as well work as a sorting column actually representing the priority of the name with the "default" or "primary" being the one with highest or lowest priority). Or we could keep the "name" column in the entity and have that be the reference to whatever name is the primary name.
Now, I can't really decide between the two. The first option feels right, but (assuming we use the "primary" column for only denoting whether it is a primary name or not) we can't use simple MySQL constraints to ensure that there is always exactly one primary name (no more, no less). Triggers could be used to check that though I believe, and if we used the column for sorting / preference we could easily put a unique index on the relation column and the sorting column to make sure there is always some name with highest priority. But we still can't enforce a name to be there for every entity.
Now, having a reference to the "primary" name from the entity while having any additional names just be there in the names table seems like the logical solution. But there's, once again, a catch: what if the name I reference as "primary" doesn't belong to this entity (i.e. it references a different instance of the entity? I could enforce this in the application fairly easily, but I'd certainly prefer the database to also enforce this, as it is its responsibility.
So tell me, what is the correct way to do this? I feel like it must be a very common scenario. Is there perhaps any other solution that I'm missing?