Question :
I was having a look at this data model to get more acknowledged for designing my own. Could someone kindly tell me what I need the Customer_Address
table for? why don’t we just settle with the Addresses
table ?
Answer :
Notice that Customer_Addresses
has a from date and a to date. The intention of the Customer_Addresses
table is to provide a history of each customer’s address over time. This is one way to do it, but there are others that could be just as valid, depending on what your system cares about.
Notice that the Addresses
table is independent. This would make sense if your system were for an organization that cared about places, like a utility company or a delivery company. Since Customer_Addresses
is a many-to-many intersection between customers and addresses, it means that your system can recognize addresses it’s heard of in the past, such as when one customer moves out of a house and a different customer moves into that same house. It also lets you track multiple customers living in the same house. Depending on your organization, that might also be important.
Another way to do this would be to keep the current address in the Customer
table and keep an Address_History
table (or similar) to remember old addresses. That could be a better design choice if historical addresses are not that interesting to your system.
You could also store all addresses (past and present) in a separate table which would be a child of the customer table. In this approach, you’d usually see either date ranges (like in your model) or a current/historical flag or something else like that which would tell you which address matters most right now.