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

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.

• I can't vouch for best practice, but I'd go with option 3. You can make it a bit if you're sure you only need the two options, and changing it to an int later (if your prognosticator is faulty) would probably be significantly easier than fixing one of the other options. Probably not a bad idea to still build the corresponding AddressType table for a reference, even if you don't code to use it. – SQLFox May 6 '13 at 21:23

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!

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)

• I'll consider this, but I think I would rather avoid it so I don't have to check which string it is in code each time I use it, and remember that the two options are 'home' and 'work' and not 'house' and 'workplace' or any other variations. – user1308743 May 6 '13 at 20:41
• @user1308743: well you would need to "remember" that when using a lookup table just as well. But the single boolean column is just awful. How do you know in 12 months that IsHome=false doesn't imply "delivery address", or "postal address" or "visiting address"? – a_horse_with_no_name May 6 '13 at 20:43
• I was considering 2) for that reason, but none of them seem all that great so I was hoping to get other input, and I do appreciate yours and am taking it into consideration. It is in a situation where there will never be another address type. – user1308743 May 6 '13 at 20:47
• I personally find 2) just as awful. From a modelling point of view it's plain wrong (I think). The address type is not two different attributes, it's a single attribute of your entity with two distinct values. And it should be modelled as such. With 2) you have to have a check constraint that ensure that not both are set to to the same value. Additionally you will need dynamic SQL if you want to query for that (because you need to use different columns for different queries). With a single attribute you can use a prepared statement and simply provide one of two distinct values. – a_horse_with_no_name May 6 '13 at 20:52
• @user1308743: The problem with Option 2 is that you have the potential for a record to have both set to true or both set to false. I don't think you'd ever do that on purpose, but hey - bugs happen! And when they do, it could be hell to figure out which should be true and which should be false. – FrustratedWithFormsDesigner May 6 '13 at 20:54

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

id
suiteOrApartment
city
postalCode

partyId
purpose {work, home, ...}


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

• Good catch! I never thought of that hypothesis on the question, but it's really the right answer since all the bases are covered up. – Fabricio Araujo May 7 '13 at 18:57