Entity Framework/SQL Server Many Foreign Keys

Question

I'm trying to decide on table design when working with Entity Framework and SQL Server.

I have a single entity (Address) that is related to many other entities (about 15).

I can either create 15 nullable FK columns in the DB or create a single unbound column (EntityPrimaryKey) and an EntityType column to track what the EntityPrimaryKey column value is for.

What issues might I have with these two different designs? Opinions on which is better?

I have a same question too. The answer is important to me :) — amiry jd
– amiry jd, Commented Oct 14, 2011 at 16:25

marc_s · Accepted Answer · 2011-10-14 16:25:21Z

3

Always use the real foreign keys that can be used to enforce referential integrity.

This "one key and a column to define the type of the key" might sound like a good idea at first - but ultimately, since you cannot establish any referential integrity, it's really quite pointless.

Over time, you'll end up with "zombie" child rows in all the tables that aren't referenced anymore. And the quality and integrity of the data is the most important aspect of a good database design to me - always make sure your data integrity is iron-clad - anything else takes a back seat to this requirement!

answered Oct 14, 2011 at 16:25

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Lajos Arpad Over a year ago

Basically I agree with you, but of course he can solve the problems mentioned by you if he uses triggers (which is a bad idea in most cases), so he doesn't necessarily end up with zombie records. Otherwise I share the same opinion as you.

Jeff Over a year ago

What kind of performance/locking implications might 15 nullable FK columns have?

Jeff Over a year ago

And I'd need to use triggers anyway to ensure that more than 1 FK column is not populated at the same time...

marc_s Over a year ago

@JeffN825: if only one of those 15 nullable FK columns is set - there's really no performance or storage overhead to be expected

Jeff Over a year ago

Thanks. Someone had mentioned deadlock possibilities.... Not sure how this would happen....but just to throw it out there....does this ring any bells?

|

Collectives™ on Stack Overflow

Entity Framework/SQL Server Many Foreign Keys

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related