This question is easily illustrated with an example. Suppose I have an Address table, with the columns: address_id, address_line_1,...,address_line_4, locality, region, postcode, country.
This table will be referenced by:
- Users (home, default billing, default shipping etc.)
- Orders (billing, shipping)
Any Address referenced more than once will not be updatable/deletable. For updates in this case, a new Address will be created and linked to as necessary.
I can think of three reasonable ways of storing information in the Address table:
- Addresses are unique. Every time a new Address is created (e.g., by a User), the entire table must be scanned to see if the Address already exists. I imagine this solution having an index supporting address_id, and an index supporting the actual Address columns (address_line_1,...,country).
- Addresses are unique at the level of the Users or Venues. If a new Address is added for a User or Venue, only the Addresses for that User or Venue need to be checked before adding another. Orders/Bookings reference these.
- Addresses are not shared at all (i.e., any row in the Address table has precisely one Owner; a User or an Order, etc.). Every time there is a new Order, the appropriate User Addresses are duplicated in the Address table and referenced by the Order.
I don't really like the third solution, but choosing between the other two is harder.
- Is it practical, at scale, to scan an entire table to see if an Address exists before adding it (Option 1)?
- Is an index supporting a combination of eight columns reasonable (Option 1)?
- Or is there value in striking a balance between row uniqueness and the amount of data to be scanned (as in Option 2)?
- Is there a point at which one option becomes better than the other?
I would appreciate any advice.