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.
| 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 columnwhen 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. |
 |
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
learntsql
524 Posts |
Posted - 2014-04-22 : 10:27:18
|
| Thank you all for the great replies. |
|
|
|
|
|
|
|
|
Foreign key design guidelines in composite PK