pros/cons of different ways to store whether a record is one of two options?

Question

I am trying to store whether an address is a Work address or a Home address. There will never be another type of address.

I'm wondering what the pros/cons are of the different ways to store this, and if there is an accepted 'style' for this type of situation which is considered best practice,

Would it be better to just have a single
1)IsHome bool column, and if it's false, I just assume it's a work?
2) or both a IsHome and IsWork column,
3) or a AddressType column which is an ID that would correspond to another table which has work and home with an ID?
4) or something I have not considered?

The third option seems a little cleaner however needing to join every time seems inefficient.

Answer 1

At the end of the day, #3 is still the BEST option. We go with what we think is simple at that point but more often than not, business will come up with another reason to add one more type of address.

Design it correctly from the get-go! Good luck!

Answer 2

If you are really sure that those types don't change (or at least not often) you can also use a variation of 3) using a column AddressType of type varchar with a check constraint that limits the values to 'work' and 'home'.

This is less flexible than a lookup table, but still better than a boolean that has some implicit meaning when set to false. And you wouldn't need the join (although I doubt a join against a table with two rows will be noticable)

Answer 3

Neither of the other 2 answers are correct.

John could live at 555 Main Street, and his wife, Shirley, could work there. It's a home and work address!

This is the correct schema:

PARTY
id
name

MAILING_ADDRESS
id
suiteOrApartment
streetAddress
city
postalCode


PARTY_MAILING_ADDRESS
partyId
addressId
purpose {work, home, ...}

You could include a fromDate and toDate in PARTY_MAILING_ADDRESS if you want to record historical addresses.