MySQL – Choosing a Good Natural Key for a Physical Mailing Address

MySQLnatural-key

I'm trying to figure out what a good natural key for a Physical Mailing (PO Box) address would be. I haven't designed the fields for the table yet, and initially I was going to go with a surrogate key and a text field for the address, allowing free input. But the other dev I'm working with is interested in having a natural key (just for this one table).

However, it's not quite making sense to me, because I'm not sure how to ensure, with an address, properly input data (when keyed in either on create or to do some kind of manual lookup). I know that you can often format the same address in more than one way.

For example:

Suite vs Ste.
Apt. 36 vs #36 vs Unit 36
1110 West 300 South vs 1110 W 300 South vs 1110 W. 300 S. vs....

All valid, but all different. Granted PO Boxes are more standardized, but I feel like it may still suffer from some of the same issues.

Any thoughts on the matter would be greatly appreciated!

Best Answer

As mentioned in the comments, free text fields make it tough to create a primary key on. And for something with as much variability as an address field, it's just not reasonably possible by itself.

This is the type of problem where if you must create a primary key for it, then you will need to use strong validation and/or an address standardization API like this one from the USPS, in your application layer before it hits the database. Even doing that, I don't think you'll have a 100% perfect solution, but it does make it more reasonably possible to then create a primary key on an address field.

Related Question