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.