Storing a Billing Address Best Practice in Orders Table

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|

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:

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.

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,
IsPhysic

StackExchange Database Administrators Q#111101, answer score: 21