0

These are the stripped down version of the schema (all of the tables are in MySql) that I am using for maintaining custom orders.

Order [Id, Shipping_AddressId, Receiving_AddressId]
Address [Id, Contact, Line1, City]

The order has references to the Shipping and Receiving Address Ids. Situation: A user A has created 10 orders using these shipping and receiving addresses. Later, the user decides to edit one of the addresses (say R1). As I want to keep the details of the original order intact, upon edit, I create a new Address and mark the old address as inactive. (Thus old orders are left untouched)

Address [Id, Contact, Line1, City, Parent_AddressId, Status]

Upon edit, I throw out notifications for everyone (User Address Book etc.) to make appropriate changes. This approach has been "okay" so far.

With a mobile app coming in that will be allowed to cache some data and synch periodically, I see more issues that would come up (there are solutions, but they are making the operations expensive).

Question: Are there other (standard) approaches to deal with this?

TJ-
  • 14,085
  • 12
  • 59
  • 90

1 Answers1

0

Originally, I used an address table with a primary key similar to how you are doing so, but experienced many issues. I've since gone away from this approach, and added "street_address_ship", "city_ship", (state or province/etc, etc, and the same for receive) to my Order table as a composite key, and my issues went away.

There appears to be a debate whether my approach is normalized or not, however, I feel it is more so. Since any address can be uniquely identified by the street_address, city, etc, then a separate primary key for this record is redundant, and should not be used.

user1032531
  • 24,767
  • 68
  • 217
  • 387
  • In my Address table, there are about 10 fields (addresses are all non-standard, include rural and include geo information). There are some fields for statistics as well (debatable). So, moving all of the information to Orders may not work. From your solution, it appears to me that I can perhaps add a "snapshot of address information that is shown to the user" as a column to the Orders table. This data would be non-queriable, though. The primary keys of the Address would still remain with the Order. Now I am thinking about long term implications of this. – TJ- Dec 12 '15 at 14:56