2

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.)
  • Venues
  • Orders (billing, shipping)
  • Bookings

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:

  1. 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).
  2. 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.
  3. 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.

Questions

  • 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.

  • 1
    I actually like the third solution the best. What kind of work load do you have to accommodate that doesn't best fit with that model? – Evan Carroll Jun 13 '17 at 16:28
  • Probably not very large for this particular web-app project (I don't really know until it's built), but I will be coming back to this question for future projects. I'm just after some insights on good design practice for this sort of thing so I don't have to think about it so much again in future. Why do you favour the third solution? For sheer simplicity (/risk management)? I suppose I just don't really like the idea that if a customer places on average 5 orders, the address table ends up 6 times larger than in needed to be. – Vorpulus Lyphane Jun 13 '17 at 16:39
  • That would not bother me, especially if I'm not going to query it. – Evan Carroll Jun 13 '17 at 16:48
  • Note - with option 2, since Bookings and Orders would refer to the address of the appropriate Users or Venues row, you have no address history (presumably, mostly relevant for Orders). If a user updates their address, then (presumably) all orders would appear to have been sent to the new address. – RDFozz Jun 13 '17 at 16:51
  • With options 1 and 2, any address referenced more than once (e.g., by both a User and an Order (placed by the user)) would not be updatable/deletable. Instead, a new row would be created (and linked to as appropriate). – Vorpulus Lyphane Jun 13 '17 at 16:56
3

Ensuring that addresses are unique is not a trivial problem. You have to assume that people enter their addresses in slightly different ways, abbreviating some parts sometimes, or omitting optional information. Just checking for exact duplicates would probably fail pretty often.

You should consider whether trying to keep addresses unique is worth the effort and complexity.

Addresses of users and addresses of orders are pretty different concepts. A user can change their current address. An order should never be retroactively changed in that way, the address there should always be the delivery address.

You could consider making addresses immutable. Changing an address would always mean creating a new address. This means you can safely point orders to the common address table, as the addresses will never change.

If a user changes their address, you simply add a new address and link it to the user. The old address can be hidden in the UI, soft-deleted or just shown as an address history. But all orders could still reference the old address because it never changes.

  • Is it practical, at scale, to scan an entire table to see if an Address exists before adding it?

That depends entirely on what you consider "at scale", it's not an issue with a few tens of thousands of rows but completely impractical with a billion rows.

  • Is an index supporting a combination of eight columns reasonable (as in Option 1)?

B-Tree indexes are limited to something around 2700 characters. Indexing very large items is typically not a good idea. In Postgres 10, which isn't released yet, you could consider a hash index for this which has a fixed size as far as I understand and should have an advantage for large columns.

But I really think you're asking the wrong question here. In your application, you should know when an address is already in the DB because the user didn't enter it from scratch, but e.g. selected it from the list of addresses already associated with their account.

You don't actually need to enforce uniqueness in your DB, if I understand your problem correctly. And if you'd need to do that, you should always use a unique constraint, which automatically uses an index.

  • I may need to add some clarification to my question. I was not talking about addresses being unique in the same way that they are unique in the real world. I was simply talking about ensuring uniqueness across (address_line_1,...,country) to keep the size of the table down. It is certainly possible that two users will still describe the same physical house with two different addresses (although steps can be taken to minimise this). This wouldn't be a problem. I'm aware of the other points raised in your answer (see the first line after the bullet points). Thank you. – Vorpulus Lyphane Jun 13 '17 at 17:28
  • 1
    I'm not asking the wrong question. The depth of my question is not being appreciated, so this answer, which has good intentions but is answering something else, is being incorrectly upvoted. All three solutions in my question can handle updates from anywhere (a User, an Order etc.) without any unwanted effects - so that's not what the question is about. The question is about at which level to set uniqueness (at the level of the table (1)? User (2)? Owner (3)?) and the balance between database table storage space used and the 'complexity' of table scans (and indexes) required. Thanks. – Vorpulus Lyphane Jun 14 '17 at 15:01

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.