Good Natural Key For a Physical Mailing Address

All we need is an easy explanation of the problem, so here it is.

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!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply