How to store "same as" data?

Question

I've got one model with 3 addresses: pickup, dropoff, and billing. I figure the billing address will usually be either the pickup or drop-off address, so from a UI perspective, I should have a "same as" option. But from a DB perspective, should I save the "same as" field, or should I duplicate the data?

Answer 1

You should have the same Id of a row from an Address table in two different columns, PickUp and DropOff. This way, you do not duplicate the address, do not use some sentinel address, and can easily query to see if the PickUp address is the same as the DropOff. If one of these changes in the future, you can always modify the Id value stored in its respective column to a new address.

Answer 2

You could create a table called 'Address' and make Pickup, Dropoff, Billing FKs to that Address table.

Answer 3

Just because an address is the same physical address doesn't mean it's the same conceptual address. Really, John Doe's address may be "123 Elm St.", but conceptually his address is "John Doe's mailing address".

In particular, for addresses I would say can and should be duplicated within a database because of this simple case: consider two people who live at the same address. Now one of them moves. If you only stored the address once, updating the "mover"s address would then update the original roommate's address as well.

But in general, consider how the data is tied to other data. If multiple things can relate to it, make sure that a change for one should impact them all.

Answer 4