CODEWITHSUNDEEP
data-modelingdimensional-modeling
Siddharth
Siddharth

Reputation: 67

Bridge table in dimensional modeling

I am familiar with creating a bridge table between facts and dimension table.

Is it a good idea to create bridge table between dimension and its multidimensional attributes?

e.g., customer has multiple phone numbers. Can I just create a customer telephone dimension which has one to many relationship with customer dimension or is creating a bridge table advisable?

Upvotes: 0

Views: 1114

Answers (1)

RADO
RADO

Reputation: 8148

Answering specifically for the multiple phones example.

I usually try to avoid bridge tables as much as possible. They are a complication of design, and keeping things simple is a better approach (although not always possible, of course).

In case of the multiple phones per customer, I would create 2 attributes:

  • Primary Phone
  • Other Phones

The first attribute will contain a main customer phone and is mandatory.

The second attribute might contain one or more other phone numbers, concatenated into a delimited string (i.e., "415-111-1111, 415-222-2222"). Such design is acceptable because you (most likely) will use these extra phones only as a descriptive information in your reports. Also, most likely you will have a varying but reasonably limited number of such phones - let's say, 0-3 or so, which means that this attribute will be either empty or contain a reasonably short string.

The above design is simple and clean and works for most situations, unless you need to perform specific analytics on the phone numbers, or if there are too many of them and they must be all used. In cases like that, I would put them into a fact table ("Customer Phones"), which might contain:

  • Customer_ID
  • Phone_Profile_ID
  • Date
  • Phone Number

Phone_Profile is a dimension that should contain phone attributes, i.e, "Phone Type" {"Land Line", "Mobile"}, "Phone Use" {"Primary", "Secondary"}, etc.

Such fact table can also be a periodic snapshot (annual, monthly etc) of all customer phones and serve as a phone catalog. However, such elaborate designs are rarely needed (unless you design for a Call Center or similar phone-heavy application).

Upvotes: 1

Related Questions