One-to-One Relationships - The Whiteboard #11

[@sherifffruitfly]

Oh thank goodness - i spent 8 minutes wondering why they were two separate tables in the first place :D

[@willreborn3673]

Great video! Should do a whiteboard session on circular dependencies in the future

[@hdegroot2]

I’m wondering whether a 1-1 relationship would be okay in following scenario: Chart of account table. I have three hierarchies on the table (balance sheet, P&L Legal, P&L Management). Each hierarchy requires about 20 level fields (key + caption). I can model this as 1) a very wide table with all the level fields; or 2) four tables, one simple chart of account table, and three coa/hierarchy tables. What to do? Anyone has a preference? Currently I’m using a single very wide table. Just wondering whether for master data tables with parallel hierarchies (eg profit centers) it would make sense to factor out the hierarchy level fields using a 1-1 relationship.

[@SQLBI]

You introduce a performance penalty with the relationship, but probably the dimension is not too large. Ideally, a single table is better. But if you don't like to use folders to organize the many fields available, you can use the approach described if it works well for your scenario.

[@filipzivkovic4866]

Great! Thanks a lot Marco, this was just what I needed!

[@StephanOnisick]

Good Explanation. Thank-you

[@SteiniJonsson]

It (almost) always comes down to a good model.

[@danjarupath]

Very nice 👍👍👍👍🙂

[@tornado1360]

Why did it use two FKs instead of only one ProductKey?

[@jpieroen]

I use 1-to-1 relations to extend a table from a published dataset (accessed via a direct query connection) with additional fields

[@SQLBI]

It makes sense for composite models, indeed.

[@richardmartin7904]

Would you be willing to do a video on how to resolve two fact tables connected to one dimension table, but one fact table does not connect to several other dimension tables. As of now I only could connect the facts tables with a many to many pairing..which worked. But this cannot be best practice can it? I also see no way to connect the other dimension tables to the one fact table ins sql. The reason this is relevant is because I need to do a calculation using both fact tables.

[@SQLBI]

Wait for the next Whiteboard episode about many-to-many relationships! In less than two weeks...

[@pandaunfortunatly3080]

Srr bro but i have to say this. Please stop using so many bodylanguages when u try to say something. I really believe you have something usefull to say, but i keep getting distracted by ur overexagerrated bodylanguage. Chillax bro, dont be so scared that people won't understand you. Peace.

[@marcorusso7472]

Unfortunately, I was on a chair. When I stand up, I also walk back and forth, increasing the noise in the communication! Sorry, it's hard to reset to the default factory settings now. Peace and love!