0

I have an Entity Framework model with a table which is expected to hold a lot of data. I am concerned about using an int primary key because I expect it to grow bigger than that. I'm considering using Int64 to get around the issue.

Here is the real kicker - I am using table per type inheritance on the table in question. So if I go with Int64, there are going to be several other tables (actually an arbitrary number of tables since I will be adding more) that have to use an Int64 primary key, even though the likelihood of them growing beyond the bounds of int are pretty slim. Seems like an inefficient solution. Thoughts?

I was thinking of going with a composite key that consists of an int ID and a subtype discriminator, probably a char. I am wondering about the performance implications for this approach. I've always preferred surrogate keys unless it's a straightforward associative entity. Is there a better approach than either of these? Which one of these is the preferable route?

UPDATE:

I want to clarify that the composite key I'm considering isn't a natural composite key. It only contains the ID and a subtype discriminator. I am interested in using it as a way to avoid the size limitations of an int primary key. My main concerns are:

1) Is there a performance concern using Int64 primary keys? Especially in a table per type inheritance where it is only necessary for the parent table, but will also have to be used in the child tables.

2) Does using a composite key (non-natural) to achieve a larger range than an int primary key offer any performance advantages over using an Int64 key to do the same thing?

lintmouse
  • 5,079
  • 8
  • 38
  • 54
  • What kind of answer do you expect but opinions? – John Dvorak Sep 05 '13 at 08:10
  • I hear you, but opinions can help me weigh the options. – lintmouse Sep 05 '13 at 08:11
  • Why don't you stub a basic application that demonstrates how you are going to use the database and profile each design? My initial opinion is not to worry about it and go with whatever makes the most sense, currently this seems to be the `bigint` solution. Databases can handle integer values quite effectively and I'd be surprised if this becomes your first major issue. – Adam Houldsworth Sep 05 '13 at 08:16
  • I prefer to have a **single** key - even if it's a surrogate and a "real" natural key would exist. It just makes joining that table **so much easier** if you have to join on just a single column - instead of having to have your 2, 3, 5, or 10 composite key columns ripple throughout your entire model - just so you can join.... – marc_s Sep 05 '13 at 08:28
  • There is no need to compromise. You do not need to pick only one. Tables can have multiple keys. Use both, the single surrogate key for joins and FKs, and the composite natural key to ensure data consistency. – Charles Bretana Sep 05 '13 at 08:39

1 Answers1

2

Please check out this SO question / answer. Basically, why not use both ? Use the integer surrogate key for joins ands foreign keys, but make sure there is a meaningful composite natural key, (if the right natural key is composite), to ensure data consistency and minimize risk of inserting duplicate rows...

BigintId  firstName LastName   Phone        Gender   Birthdate
  1         Bob      Smith    111 234-5678    M     1 jan 1978
  2         Bob      Smith    111 234-5678    M     1 jan 1978
  3         Bob      Smith    111 234-5678    M     1 jan 1978
  4         Bob      Smith    111 234-5678    M     1 jan 1978

Are these really different entities, just because they have a different id ? Only if we blindly redefine different to simply mean it has a different Key...

Community
  • 1
  • 1
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216