Make the unique key long enough to allow for reasonable duplication of names in the real world
For example, for a table of people, a unique key of country/state/city/address might be a good choice. This allows for two people in the same city to have the same name as long as they live at different addresses.
Make the unique key short enough in case you don’t have all the information for the key always available.
Using the same example, you might not have an address for everyone, so unique key becomes useless. You might as well shorten the unique key to country/state/city and require that people in the same city have slightly different names (maybe using a middle initial or a suffix like Jr. or Sr.)
Use a unique key that makes the most sense most of the time.
Using a unique key like country/state/metro/city might be bad since many cities might not fall into a popular metro. Then, your data will become bad when you have two of the exact same city, but one row has the metro filled in and the other does not. You might as well leave metro off the unique key since it will not be there ALL of the time.
In the US, a city will always belong to a state, and a state will always belong to the US, so country/state/city is a good unique key in this case.
Don’t make the unique key too short.
A unique key of country/state will be too short because there will be many people with the same name in the same state.
This is not a perfect solution.
But, it might fall into the “good enough for now” category.
