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|

3 Answers 3


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 previously linked post 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. 

    CustomerNumber      INT      NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,  
    CreatedDateTime     DATETIME NOT NULL,
    CONSTRAINT Customer_PK PRIMARY KEY (CustomerNumber)

    AddressId           INT      NOT NULL,
    SpecificAttribute   CHAR(30) NOT NULL,
    ParticularAttribute CHAR(30) NOT NULL,  
    CreatedDateTime     DATETIME NOT NULL,  

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)  

    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 have 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 involved in SELECT queries whenever necessary.

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.

In turn, 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 situation in which a Customer is not using that Address anymore and hence it cannot be used for new Orders.

Data manipulation factors

Modifiying and erasing

If a Customer wants to change one or more pieces of information about a given “current” 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). I suggest that you carry out this process as a single unit of work inside an individual ACID TRANSACTION.

UPDATE operations on the two History tables should be forbidden (trying to “change” history can even have negative legal implications), since each row retained in that “kind” of tables represents a fact that happened in the past, thus it cannot be modified and a row about it should not be altered.

As for DELETEs on these tables, there are very specific cases where they must be enabled in order to comply with, say, confidentiality laws/policies —but that, as usual, depends entirely on the exact requirements of the business environment of significance—.

Retrieval and physical-level tuning

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, taking care of the optimization of the respective physical level processing (by dint of, e.g., index tunning, hardware upgrades, proper software settings, etc.).

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 it—, but that does not necessarily mean that the modelers have to design the significant parts of the database in question in correspondance with said perception. 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 data 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 (which, naturally, is part of the external level of abstraction of a computerized system).


On the other hand, I have seen some systems in which every time that a new Order is effectuated the relevant Address information has to be entered (sometimes 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 data duplication, 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.

Comment interactions

Could you explain me the reason we need an Address entity? wouldn't be enough just have CustomerAddress? – @Cristiano, on Mar 11 at 19:38:05Z

In this specific case, the original poster —with a now deleted account— presented a scenario involving an Address entity type that holds four discrete properties of interest, i.e., AddressID, AddressType, AddressLine1 and AddressLine2 —as shown in the question itself—, each of which (a) has a particular set of valid values, (b) involves a potential exclusive set of constraints and (c) implies values that have to be manipulated separately. In addition, (d) any given Address instance —i.e., the proper Address entities— may be associated with n occurrences of entities of other types; therefore, (e) Address is an entity type in its own right.

There very well may be other situations where, depending on the exact business requirements, a Customer.Address should be treated as a single property, in which case its values have to be handled atomically at the logical level of abstraction, i.e., you always manipulate every Address value as a whole, never perform operations (INSERTs, UPDATEs, DELETEs, SELECTs) on certain value part(s) only. Of course, if required, one should first set up constraints for the corresponding atomic Customer.Address column, since these would not be necessary for column “fractions”. One of these situations could be the scenario discussed in the prior section that is entitled “Alternative”.

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? Commented Nov 12, 2018 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
    Commented Nov 12, 2018 at 16:31
  • 2
    @Shadrix I just edited the post, in case you want to take a look.
    – MDCCL
    Commented Nov 15, 2018 at 2:09
  • 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
    Commented Apr 12, 2019 at 19:56
  • 1
    @Cristiano I just resplied to your question in the answer itself, in the new section entitled “Comment interactions”.
    – MDCCL
    Commented Nov 19, 2020 at 21:57

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.


I'd like to answer this as a better explanation of/improvement over @Erik's answer.

Say your db includes these 3 tables :

  • user_table
  • address_table
  • orders_table

  In address_table, the Auto Increment field, id relates to both say, user_table.adr_id and orders_table.addr_id.

 When a user creates his account and enters new address, it'll be stored in address_table as a new row and the row's id(say 10) will be referenced in user_table.adr_id as 10.

 When the user orders something, the program loads the current address id from user_table.adr_id and it'll be stored in orders_table.addr_id as 10.

 Now, sometime after, if the user decides the change his address, instead of UPDATE address_table WHERE id==10...,, you create a new row INSERT into address_table....., the new data (address_table.id will be say 11) and update user_table.adr_id=11.

 So the next time user orders something, orders_table.addr_id will get new address value. But past orders will still retain the old address data (as row 10 is not deleted and is still referenced from past orders in orders_table)

I don't know if it's advisable or not, but if you store the user's id in a field in address_table, you can even gather all the user's past addresses used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.