Are one to one relationships bad?

View previous topic View next topic Go down

Are one to one relationships bad?

Post  falcon00 on Mon Dec 09, 2013 5:06 pm

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?


Last edited by falcon00 on Mon Dec 09, 2013 6:05 pm; edited 1 time in total

falcon00

Posts : 17
Join date : 2013-11-07

View user profile

Back to top Go down

Re: Are one to one relationships bad?

Post  Jeff Smith on Mon Dec 09, 2013 5:22 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Are one to one relationships bad?

Post  falcon00 on Mon Dec 09, 2013 5:43 pm

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

falcon00

Posts : 17
Join date : 2013-11-07

View user profile

Back to top Go down

Re: Are one to one relationships bad?

Post  ngalemmo on Mon Dec 09, 2013 6:28 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Are one to one relationships bad?

Post  Jeff Smith on Mon Dec 09, 2013 7:16 pm

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.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Are one to one relationships bad?

Post  falcon00 on Mon Dec 09, 2013 7:18 pm

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.

falcon00

Posts : 17
Join date : 2013-11-07

View user profile

Back to top Go down

Re: Are one to one relationships bad?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum