Storing a Billing Address Best Practice in Orders Table

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 Customer^a 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. 

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

Alternative

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

Storing a Billing Address Best Practice in Orders Table

Expository IDEF1X diagram

Illustrative SQL-DDL logical layout

Data manipulation factors

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

Alternative

Comment interactions

Suggested reading

Tags:

Database Design

Best Practices

Related

Recent Posts