10

Can someone help me understand this user's answer for a CustomerLocation table. I really want a good method for storing addresses in the orders table.

What I'm looking for is how I can set up my addresses so when I edit them, the order is not effected by the fact that a customer updates his address or relocates.

As it stands my schema looks similar to:

 Person           |EntityID|
 EntityAddress    |EntityID|AddressID|
 Address          |AddressID|AddressType|AddressLine1|AddressLine2|
 Order            |OrderID|BillingAddressID|
15

Conceptually speaking, although in your business environment Order and Address are ideas that are closely associated, they are in effect two separate entity types, each with its own set of applicable properties (or attributes) and constraints.

Therefore, as previously stated in comments, I agree with @Erik, and you should organize the logical layout of your database declaring among other elements:

  • one discrete table to keep Address pieces of information;
  • one table to retain Customer-specific details;
  • one table to enclose Order data points; and
  • one table to contain facts about the associations between Customer(s) and Address(es);

as I will exemplify below.

Expository IDEF1X diagram

A picture is worth a thousand words, so I created the IDEF1X diagram shown in Figure 1 to illustrate some of the possibilities opened by my suggestion:

Figure 1 - Customers, Orders and Addresses Expository IDEF1X Diagram

Customer, Address and their associations

As demonstrated, I portrayed an association with a many-to-many (M:N) cardinality ratio between the entity types Customera and Address; this approach would provide future flexibility because, as you know, a Customer can keep multiple Addresses over time, or even simultaneously, and the same Address can be shared by multiple Customers.

A particular Address can be used in several ways by one-to-many (1:M) Customers; e.g., it can be defined as Physical, and/or it can be set for Shipping, and/or for Billing. Perhaps, the same Address instance can serve each of the aforementioned purposes at the same time, or it may be covering two uses while a different Address occurrence covers the remaining one.

a In some business environments, a Customer can be either a Person or an Organization (situation that would imply a slightly distinct arrangement, as detailed in this answer about a supertype-subtype structure) but with the objective of providing a simplified example, I decided not to include that possibility here. In case you need to cover that situation in your database, the post of the previous link shows the method to solve said requirement.

Order, Address, CustomerAddress and Address Roles

Commonly, an Order requires only two kinds of Addresses, one for Shipping and one for Billing . In this way, the same Address instance could fill both Roles for an individual Order, but each Role is pictured by the respective property, i.e., ShippingAddressId or BillingAddressId.

Order is connected with Address via the CustomerAddress associative entity type with the aid of two multi-property FOREIGN KEYs, i.e.,

  • (CustomerNumber, ShippingAddressId), and (CustomerNumber, BillingAddressId),

both pointing to the CustomerAddress multi-property PRIMARY KEY shown as

  • (CustomerNumber, AddressId)

… which helps to represent a business rule that stipulates that (a) an Order instance must be linked exclusively with (b) Address occurrences previously associated with the specific Customer who made that Order, and never with (c) a random non-Customer-related Address.

History for (1) Address and for (2) the CustomerAddress association

If you want to supply the possibility of modifying Address pieces of information, then you have to keep track of all the data changes. In this manner I depicted Address as an “auditable” entity type that maintains its own AddressHistory.

Since the nature of a connection between a Customer and an Address can also suffer one or more modifications, I have as well depicted the possiblity of handling such an association as an “auditable” one by virtue of the CustomerAddressHistory entity type.

In this respect, various factors dealt with in Q & A no. 1 and Q & A no. 2, —both about enabling temporal capabilities in a database— are really relevant.

Illustrative SQL-DDL logical layout

Consequently, in terms of the diagram displayed and explained above, I declared the following logical-level arrangement (which you can adapt to meet your needs with exactitude):

-- You should determine which are the most fitting 
-- data types and sizes for all your table columns 
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the 
-- most convenient INDEX strategies based on the exact 
-- data manipulation tendencies of your business domain.

-- As one would expect, you are free to utilize 
-- your preferred (or required) naming conventions. 

CREATE TABLE Customer (
    CustomerNumber      INT      NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,  
    CreatedDateTime     DATETIME NOT NULL,
    -- 
    CONSTRAINT Customer_PK PRIMARY KEY (CustomerNumber)
);

CREATE TABLE Address (
    AddressId           INT      NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,  
    CreatedDateTime     DATETIME NOT NULL,  
    -- 
    CONSTRAINT Address_PK PRIMARY KEY (AddressId)
);

CREATE TABLE CustomerAddress (
    CustomerNumber  INT      NOT NULL,  
    AddressId       INT      NOT NULL,
    IsPhysical      BIT      NOT NULL,
    IsShipping      BIT      NOT NULL,  
    IsBilling       BIT      NOT NULL,
    IsActive        BIT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,  
    -- 
    CONSTRAINT CustomerAddress_PK           PRIMARY KEY (CustomerNumber, AddressId),
    CONSTRAINT CustomerAddressToCustomer_FK FOREIGN KEY (CustomerNumber)
        REFERENCES Customer (CustomerNumber),
    CONSTRAINT CustomerAddressToAddress_FK  FOREIGN KEY (AddressId)
        REFERENCES Address  (AddressId)  
);

CREATE TABLE MyOrder (
    CustomerNumber      INT      NOT NULL,  
    OrderNumber         INT      NOT NULL,
    ShippingAddressId   INT      NOT NULL,
    BillingAddressId    INT      NOT NULL,    
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,  
    OrderDate           DATE     NOT NULL,
    CreatedDateTime     DATETIME NOT NULL,  
    -- 
    CONSTRAINT Order_PK                  PRIMARY KEY (CustomerNumber, OrderNumber),
    CONSTRAINT OrderToCustomer_FK        FOREIGN KEY (CustomerNumber)
        REFERENCES Customer        (CustomerNumber),
    CONSTRAINT OrderToShippingAddress_FK FOREIGN KEY (CustomerNumber, ShippingAddressId)
        REFERENCES CustomerAddress (CustomerNumber, AddressId),
    CONSTRAINT OrderToBillingAddress_FK  FOREIGN KEY (CustomerNumber, BillingAddressId)
        REFERENCES CustomerAddress (CustomerNumber, AddressId)          
);

CREATE TABLE AddressHistory (
    AddressId           INT      NOT NULL,
    AuditedDateTime     DATETIME NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,  
    CreatedDateTime     DATETIME NOT NULL,  
    -- 
    CONSTRAINT AddressHistory_PK          PRIMARY KEY (AddressId, AuditedDateTime),
    CONSTRAINT AddressHistoryToAddress_FK FOREIGN KEY (AddressId)
        REFERENCES Address  (AddressId)    
);

CREATE TABLE CustomerAddressHistory (
    CustomerNumber  INT      NOT NULL,  
    AddressId       INT      NOT NULL,
    AuditedDateTime DATETIME NOT NULL,    
    IsPhysical      BIT      NOT NULL,
    IsShipping      BIT      NOT NULL,  
    IsBilling       BIT      NOT NULL,
    IsActive        BIT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,  
    -- 
    CONSTRAINT CustomerAddressHistory_PK                  PRIMARY KEY (CustomerNumber, AddressId, AuditedDateTime),
    CONSTRAINT CustomerAddressHistoryToCustomerAddress_FK FOREIGN KEY (CustomerNumber, AddressId)
        REFERENCES CustomerAddress (CustomerNumber, AddressId)
);

If you want to take a look, I tested it in this db<>fiddle that runs on SQL Server 2017.

The History tables

The following excerpt from your question is very important:

What I'm looking for is how I can set up my addresses so when I edit them, the order is not affected by the fact that a customer updates his address or relocates.

The AddressHistory and CustomerAddressHistory tables aid in ensuring that an Order is not affected by Address changes, as all the “previous” rows should be retained in the respective History table and may be queried when necessary. UPDATE and DELETE operations on these two tables should be forbidden (trying to change history can even have negative legal implications).

The Interval encompassed between the values enclosed in AddressHistory.CreatedDateTime and AddressHistory.AuditedDateTime stands for the entire Period during which a certain “past” Address row was deemed “present”, “current” or “effective”. Similar considerations apply to the CustomerAddressHistory rows.

The CustomerAddress.IsActive BIT (boolean) column is meant to point out whether a certain Address row is “usable” by a Customer row or not; e.g., if it is set to ‘false’ it would convey the fact that a Customer is not using that Address anymore and hence it cannot be used for new Orders.


Note: On the other hand, I have seen some systems in which every time that a new Order is effectuated the Address information has to be entered (some times repeatedly), and the Address(es) used for past Orders are never erased (hence the Orders are not affected by Address changes).

This course of action can decidedly involve substantial volumes of redundancy, but it is a possibility that —depending on the exact informational requirements of your business domain— could work, so you might like to evaluate its pros and cons as well.


Data retrieval

The “present”, “current” or “effective” version of an Address occurrence must be contained as a row in the Address table, but SELECTing the previous “states” of an Address FROM the AddressHistory (or from CustomerAddressHistory) table is easy, and it may be an interesting exercise to enhance your SQL coding skills.

With respect to one of the situations that you mentioned in comments, if you want to retrieve the “second to last version” of an individual Address row FROM its AddressHistory, you have to take into account the MAX(AddressHistory.AuditedDateTime) and the AddressHistory.AddressId that matches the particular Address.AddressId value at hand.

In this regard —at least when building a relational database—, it is quite convenient to first define the corresponding conceptual schema (based on the applicable business rules) and after that declare its subsequent logical DDL arrangement. Once you obtain stable and reliable versions of these fundamental elements (which, of course, can evolve over time), it is time to analyze and determine the best ways to manipulate (via INSERT, UPDATE, DELETE and SELECT operations or combinations thereof) the concerning data.

End-users perception, views and application program(s) assistance

Evidently, at the external level of abstraction, Address information is perceived (by end-users) as being part of an Order, and there is nothing wrong with that, but that does not mean that the modelers have to design the significant parts of the database in question like that. On this point, if there is the need to, e.g., print a “full” Order (very feasible), you can “reproduce” it on-demand with the help of a few JOIN operators and WHERE clauses (considering the concerning validity period, etc.) maybe fixed in views for future consumption, sending the pertinent result set to the related application program(s) that, in turn, can enhance its formatting as necessary.

Of course, the application program(s) will be very helpful too when an Order is being effectuated; e.g., a desktop/mobile app window or a web page can:

  • display only the Address(es) that the involved Customer has established as “usable” (via CustomerAddress.IsActive);
  • list together all the Addresses that the Customer has enabled for billing service (via CustomerAddress.IsBilling); and
  • group all the Addresses that the Customer has defined for shipping service (via CustomerAddress.IsShipping);

facilitating in this manner all the involved processes at the GUI (i.e., the external level of abstraction of a computerized system).


Suggested reading

You requested (in now removed comments) some pointers about sound database literature; therefore, as for theoretical material, I highly advise that you read all the work written by Dr. E. F. Codd, a Turing Award recipient and, of course, the sole originator of the relational model of data (maybe now more relevant than ever). This list includes some of his tremendously influential articles and papers.

Two important works that are not comprised in the aforesaid list are, precisely, his ACM Turing Award Lecture entitled Relational Database: A Practical Foundation for Productivity, from 1981, and his book denominated The Relational Model for Database Management: Version 2, which was published in 1990.

On the conceptual design front, Integrated Definition for Information Modeling (IDEF1X) is a seriously recommendable technique that was defined as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST).

  • 1
    Sorry, I know the post is older, but why are you referencing (for example: REFERENCES Address (AddressId)) in MyOrder? Why not CustomerAddress? – Shadrix Nov 12 '18 at 16:09
  • 1
    No worries, and nice catch: As a matter of fact, both MyOrder.ShippingAddressId and MyOrder.BillingAddressId must make a reference to CustomerAddress.AddressId (and not to Address.AddressId); in this way one ensures that an Order can exclusively be associated with the Address(es) previously connected with the Customer who made that Order. The diagram suggests this arrangement, so the DDL will be more accurate. Thanks for requesting that clarification. – MDCCL Nov 12 '18 at 16:31
  • 2
    @Shadrix I just edited the post, in case you want to take a look. – MDCCL Nov 15 '18 at 2:09
  • @MDCCL When you said no UPDATE and DELETE on History table, should it be same for Address table? What if Customer Orders something and then changes only the postcode or city just one field. We should insert the existing address into History and then make a new insert into Address table, right? – Mike Ross Apr 12 at 5:31
  • 1
    OTOH, if a Customer wants to change one or more pieces of information about a given Address, one must ensure that (a) the corresponding Address row that was “present” until the modification took place is INSERTed into the AddressHistory table, and also that (b) the Address row in question is UPDATEd with the new value(s). It'd be advantageous to carry out this process as a single unit of work inside a transaction. – MDCCL Apr 12 at 19:56
4

This answer was compiled from comments to the question.

One solution would be to use a FK to the address table in the order table. That will let you see the addresses that were used for the order, and decouples the address from the User's current address.

In order to make this work you would have to insert a new address and link that new address to the User table. This means that addresses are write once and the edit is an illusion to the end user. You can effectively store the history of all addresses a user was associated with by moving the association from the User table to an association table with a time stamp. That would give you a history of edits/addresses, and maintain immutable data in the Address table.

@MDCCL stated:

[you should] organize your database structure having one table for retaining Order related data, and another table for keeping the Address info. And, yes, you can definitely have a table representing many-to-many relationship between these two entity types. If a User can change his Address(es) attributes, then you have to keep track of such modifications, thus you should enable the corresponding AddressHistory. This post is related to the latter aspect.

MDCCL also gave an overview on how to find the current address for a user here:

In order to grab the latest version of a History table you have, you have to take into account the MAX(AuditedDateTime) of the corresponding AddressId. The first step is modeling/designing your best possible conceptual and logical arrangements, the second step is finding the proper ways to INSERT, UPDATE, DELETE and SELECT your data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy