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!

  • 2
    What if a husband and wife share the same PO Box? – Rick James Mar 23 at 5:23
  • Fuzzy PKs and especially fuzzy and UPDATABLE primary keys are a nightmare. – bobflux Mar 23 at 8:01
  • @RickJames - in this case, that's unimportant. It's a single box for a very specific application, not shared in any way. – Mr Mikkél Mar 23 at 14:42
  • How about Open Location Codes? – mustaccio Mar 23 at 15:09
  • @mustaccio - would that work with PO Boxes where they are all, essentially, in the same physical building, if not same location? – Mr Mikkél Mar 23 at 15:47

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.