Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Foreign key design guidelines in composite PK

Author  Topic 

learntsql

524 Posts

Posted - 2014-04-21 : 03:11:24
Hi all,

I am started designing database for one of the applications.
I have created master and child tables, now i need to establish a relationship.

some of my master table contains multiple columns as a key column
when i have to establish a relationship on this table with any fact table, i have to take all these part of key columns to be included in a fact table.

can some one tell me which approach is preffered in this kind of design.

Thanks in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-21 : 09:03:45
I would not have a composite PK. Rather, I would have a surrogate PK as int identity(1,1). Then have your FKs refer to that. In your master table, you can set up a unique index on the current composite key columns to enforce uniqueness.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-21 : 12:15:42
The intersection key should be:
parent key column(s) followed by child key column(s).

Don't worry about multiple key columns (as long as it's not extreme). That's still the right way to key the data for performance.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-21 : 12:45:03
"Don't worry about multiple key columns (as long as it's not extreme)."

You should worry about it. You're not just keeping the multiple columns in the fact table, but also in all master (dim?) tables that you want to join with the fact table. Keeping multiple columns as the PK will increase the size of those tables to no one's benefit and generally add to the complexity with no upside. Generally in a DW scenario, you do not want any business keys in the fact dimension at all, ever. Fact tables tend to go from large to ginormous faster than you think.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-21 : 13:18:00
If you are using a Dimensional model for a DW, then you probably have bigger issues anyway. But, the general rule for dimensional models is to surrogate.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-22 : 10:16:01
I agree: if this is a true data warehouse, then surrogate, but you'd have a different structure anyway.

If it's a data mart of other reporting structure, such that you need to do constant joins based on the key matches, I'd strongly urge you to use your existing keys and not surrogate.
Go to Top of Page

learntsql

524 Posts

Posted - 2014-04-22 : 10:27:18
Thank you all for the great replies.
Go to Top of Page
   

- Advertisement -