One of the things that I found the most confusing when I started in on dimensional design is the question of whether a series of attributes (fields that describe the subject of a dimension) should go in the dimension I’m designing or another new dimension. If you’ve found yourself traveling some of the same roads I have you might be nodding along right now. This is a confusing topic. Let’s see what we can make of it together.
A dimension table is made up of a series of attributees, or fields that describe the focus of the table. Let’s imagine a Person table like the one that follows …
The question that this design brings to my mind is … should the home address AND the shipping address be stored in THIS table? No argument that both should be saved, but where? In this example, we have a series of attributes that describe the Person (FirstName, LastName …). Notice the addresses? In this design, we can capture one home address and one shipping address per personID. We’ll address how to handle changes in another post.
When you find a scenario like this you should ask yourself, do these sets of attributes have a relationship with the subject of this table OUTSIDE any business process we might be modeling? In this example, I (Tim Costello) do indeed live in Euless Texas regardless of any business process I might be a part of, however … my shipping address might change from business transaction to transaction, or even sometimes from process to process. Another classic example is Customer and SalesPerson. You might be tempted to store a salesperson associated with a customer in the customer table, but that value (the SalesPerson) might be different from sale to sale. Really, it’s the SALE that defines the relationship between customer and salesperson. It’s the SHIPMENT that defines the relationship between customer and shipping address. The sale and the shipment have no effect on my home address, they DO have a lot to do with my shipping address.
To wrap up, in the scenario above I would leave the home address fields as attributes of the Person and move the shipping address fields off to another table that would be associated with the person through a record in a fact table. This way, each person can have many records each with the same or a different shipment location.
What do you think? Does this make sense? It’s a tricky one. I look forward to your comments. Maybe you can share how YOU handle problems like this … I’d love to see alternate approaches.