Are one to one relationships bad?

[falcon00]

I have data about an invoice. One record is one invoice. I spit it up between descriptive columns and measures. I took the descriptive columns and made a dimension table and of course took the measures and stuck them in a fact table. Since there are no line items like in a traditional invoice model this creates a one to one relationship between dim_invoice and fact_invoice. Is it legal to do this?

[Jeff Smith]

I have a similar situation with a Check Fact Table. The Dimension is at the Check level and so is the Fact Table. The Check Dimension includes dates such as the Check written date, print date, mailed date, cleared date, etc.

[falcon00]

Did you implement it?. Did the world come to an end or was it fine?

[ngalemmo]

As for legality, I think they tried to outlaw it in New York City, but it was buried in the law banning large soft drinks which was overturned in the courts.

Generally speaking, it is consider poor practice. One of the reasons for this is, what's the point of having a dimension? Either way you wind up with poor performance, so why bother with the join?

What you should do is break the invoice attributes into a cluster of smaller dimensions and carry those foreign keys in the fact table. Treat the invoice number itself as a degenerate dimension.

[Jeff Smith]

The invoice dimension is a one to one with the invoice fact. But what if there was another fact that contained the items from the invoice. In that case, it would not be a one to one relationship between the fact and dimension. And, let's say the invoice dimension contained things like invoice date, filled date, shipped date, assuming the entire invoice are always shipped together, and the dates were updated.

Same with a check. Sure, we could make the check number a degenerate dimension and then create bank dimensions, etc. but we pay dentists 1 check a week for multiple claims. So if I have a check dimension that is one to one with the check fact, it's many to many with claim fact. Plus, because the check dimension has mailed date, cleared date, and whether it was voided, that information is also linked to the claim. And while the check dimension and check fact are one to one, there are other dimensions on the check fact such as the payment cycle, which can be important when reconciling to the GL.

[falcon00]

Thanks to the both of you for your input! When I took a hard look at it my invoice dimension completely evaporated into smaller dimensions.

[Sponsored content]