18

Within one Web application I am working on, all database operations are abstracted using some generic repositories defined over Entity Framework ORM.

However, in order to have a simple design for the generic repositories, all involved tables must define an unique integer (Int32 in C#, int in SQL). Until now, this has been always the PK of the table and also the IDENTITY.

Foreign keys are heavily used and they reference these integer columns. They are required for both consistency and for generating navigational properties by the ORM.

The application layer typically does the following operations:

  • initial data load from table (*) - SELECT * FROM table
  • Update - UPDATE table SET Col1 = Val1 WHERE Id = IdVal
  • Delete - DELETE FROM table WHERE Id = IdVal
  • Insert - INSERT INTO table (cols) VALUES (...)

Less frequent operations:

  • Bulk insert - BULK INSERT ... into table followed (*) by all data load (to retrieve generated identifiers)
  • Bulk delete - this is a normal delete operation, but "bulky" from ORM's perspective: DELETE FROM table where OtherThanIdCol = SomeValue
  • Bulk update - this is a normal update operation, but "bulky" from ORM's perspective: UPDATE table SET SomeCol = SomeVal WHERE OtherThanIdCol = OtherValue

*all small tables are cached at application level and almost all SELECTs will not reach database. A typical pattern is initial load and lots of INSERTs, UPDATEs and DELETEs.

Based on current application usage, there is very small chance of ever reaching 100M records in any of the tables.

Question: From a DBA's perspective, are there significant problems I can run into by having this table design limitation?

[EDIT]

After reading the answers (thanks for the great feedback) and referenced articles, I feel like I have to add more details:

  1. Current application specifics - I did not mention about current web application, because I want to understand if the model can be reused for other applications as well. However, my particular case is an application that extracts lots of metadata from a DWH. Source data is quite messy (denormalized in a weird way, having some inconsistencies, no natural identifier in many cases etc.) and my app is generating clear separated entities. Also, many of the generated identifiers (IDENTITY) are displayed, so that the user can use them as business keys. This, besides a massive code refactoring, excludes usage of GUIDs.

  2. "they should not be the only way to uniquely identify a row" (Aaron Bertrand♦) - that is a very good advice. All my tables also define an UNIQUE CONSTRAINT to ensure that business duplicates are not allowed.

  3. Front-end app driven design vs. database driven design - design choice is caused by these factors

    1. Entity Framework limitations - multiple columns PKs are allowed, but their values cannot be updated

    2. Custom limitations - having a single integer key greatly simplifies data structures and non-SQL code. E.g.: all lists of values have an integer key and a displayed values. More important, it guarantees that any table marked for caching will be able to put into a Unique int key -> value map.

  4. Complex select queries - this will almost never happen because all small (< 20-30K records) tables data is cached at application level. This makes life a little harder when writing application code (harder to write LINQ), but the database is hit much nicer:

    1. List views - will generate no SELECT queries on load (everything is cached) or queries that look like this:

      SELECT allcolumns FROM BigTable WHERE filter1 IN (val1, val2) AND filter2 IN (val11, val12)
      

      All other required values are fetched through cache lookups (O(1)), so no complex queries will be generated.

    2. Edit views - will generate SELECT statements like this:

      SELECT allcolumns FROM BigTable WHERE PKId = value1
      

(all filters and values are ints)

  • You might find these posts of relevance, as some logical, physical and practical aspects are discussed with regards to the use of columns with system-generated surrogate values. – MDCCL Feb 3 '17 at 16:30
19

Other than additional disk space (and in turn memory usage and I/O), there's not really any harm in adding an IDENTITY column even to tables that don't need one (an example of a table that doesn't need an IDENTITY column is a simple junction table, like mapping a user to his/her permissions).

I rail against blindly adding them to every single table in a blog post from 2010:

But surrogate keys do have valid use cases - just be careful not to assume that they guarantee uniqueness (which is sometimes why they get added - they should not be the only way to uniquely identify a row). If you need to use an ORM framework, and your ORM framework requires single-column integer keys even in cases when your real key is either not an integer, or not a single column, or neither, make sure that you define unique constraints/indexes for your real keys, too.

  • Thanks for the quick reply. Yes, the application uses an ORM (EF). It does not require single integer column keys, but I have introduced this restriction to make some generic operations much easier (design-wise). Also, all application caches stores everything in maps (dictionaries) for fast retrievals by key and the key must be unique. Since, I have chosen ints over guids, I am forced to use IDENTITY for any table that I insert into. For fixed values tables, IDENTITY is not required. – Alexei Feb 3 '17 at 16:17
  • I think some cases that call for avoiding the uniqueness check on natural keys exist. As someone who works with GIS data, the one that comes to mind immediately is where the natural key is either just the geometry itself or the geometry plus some foreign key. Looking things up by an exact geometry is always going to be impractical, so a uniqueness constraint on it is unlikely to help much and may have performance drawbacks. The same could be true if part of the natural key is a long text column. But I agree: whenever practical, yes, a unique constraint on the natural key should be applied. – jpmc26 Feb 5 '17 at 6:05
13

From my experience, the main and overwhelming reason to use a separate ID for every table is the following:

In almost every case my customer swore a blood oath in the conception phase that some external, "natural" field XYZBLARGH_ID will stay unique forever, and will never change for a given entity, and will never be re-used, there eventually appeared cases where the Primary Key properties were broken. It just does not work out that way.

Then, from a DBA point of view, the things that makes a DB slow or bloated are certainly not 4 bytes (or whatever) per row, but things like wrong or missing indexes, forgotten table/index reorganizations, wrong RAM/tablespace tuning parameters, neglecting to use bind variables and so on. Those can slow down the DB by factors of 10, 100, 10000... not an additional ID column.

So, even if there were a technical, measurable downside of having an additional 32 bit per row, it is not a question of whether you can optimize the ID away, but whether the ID will be essential at some point, which it will be more likely than not. And I'm not going to count off all the "soft" benefits from a software development stance (like your ORM example, or the fact that it makes it easier for software developers when all IDs by design have the same datatype and so on).

N.B.: note that you do not need a separate ID for n:m association tables because for such tables the IDs of the associated entities should form a primary key. A counterexample would be a weird n:m association which allows multiple associations between the same two entities for whatever bizarre reason - those would need their own ID column then, to create a PK. There are ORM libraries which cannot handle multi-column PKs though, so that would be a reason to be lenient with the developers, if they have to work with such a library.

  • 2
    "weird n:m association which allows multiple associations between the same two entities" VERY common in real life. For example a person owns a car, then the requirements changes to recored when the ownership started and ended, (A person can sell a car and buy it back later, and crash your software....) – Ian Ringrose Feb 4 '17 at 16:44
  • Yup, something like that, @IanRingrose. – AnoE Feb 4 '17 at 19:28
6

If you invariably add a meaningless extra column to every table and reference only those columns as foreign keys then you will almost inevitably make the database more complex and difficult to use. Effectively you will be removing data of interest to users from the foreign key attributes and forcing the user/application to do an extra join to retrieve that same information. Queries become more complex, the optimizer's job becomes harder and performance may suffer.

Your tables will be more sparsely populated with "real" data than they would otherwise have been. The database will therefore be more difficult to comprehend and verify. You may also find it hard or impossible to enforce certain useful constraints (where constraints would involve multiple attributes that are no longer in the same table).

I'd suggest you choose your keys more carefully and make them integers only if/when you have good reasons to. Base your database designs on good analysis, data integrity, practicality and verifiable results rather than relying on dogmatic rules.

  • 1
    And yet many systems do have synthetic integer primary keys on every table (almost every Ruby on Rails app ever written, for example), without suffering from such problems. They also never suffer from the problem of having to push changes to primary keys (that were never supposed to happen) to all of the foreign key tables. – David Aldridge Feb 5 '17 at 14:00
  • 2
    The question asked for possible disadvantages, hence my answer. I don't deny that surrogate keys can make sense if used wisely. But I have seen tables with 3,4,5 (or many more) meaningless foreign keys which therefore required 3,4,5 or more joins to get useful results out of them. A more pragmatic design might have required no joins at all. – nvogel Feb 5 '17 at 15:42
  • 1
    I'm not convinced that it's the execution of such queries that is the primary problem that people have with such a design -- it's the writing of the query that they often object to. – David Aldridge Feb 5 '17 at 15:51
5

In my experience with various databases, an Integer primary key is always better than the applications that have no keys defined at all. Or that have keys that join half a dozen varchar columns in awkward ways that aren't logical... (sigh)

I've seen applications that switched from integer PKs to GUIDs. Their reason for doing so was because there was a need to merge data from multiple source databases in certain cases. The developers switched all of the keys to GUIDs so that the merges could happen without fear of data collisions, even on tables that weren't part of the merge (just in case those tables ever became part of a future merge).

I'd say an integer PK is not going to bite you unless you plan to merge data from separate sources or you might have data that goes beyond your integer size limits -- it is all fun and games until you run out of space for inserts.

I will say, though, that it can make sense to set your clustered index on a column other than your PK, if the table will be queried more frequently that way. But that's an outliar case, especially if the bulk of updates and selects are based on the PK values.

  • 2
    Sounds like a terrible justification to change all the keys to guids. I currently work with a database which uses guids for all the surrogate keys.. its not fun. – Andy Feb 4 '17 at 20:49
  • 2
    No. Using GUIDs is not fun. I don't like them, but I respect their value in certain use cases. – CaM Feb 6 '17 at 13:02
2

Putting aside:

  • The religious wars (google surrogate vs natural key)
  • The separate issue of what clustered indexes to define on your tables
  • The viability of caching all your data

Provided you're using bulk delete/update where appropriate, and have indexes to support such operations, I don't think you'll run into trouble due to the PK standard you use.
It's possible that if you later have EF generate queries with joins etc, that they won't be as efficient as they would be with a natural key based repository, but I don't know enough about that area to say for sure either way.

  • 4
    I can't think of a single case where a join on a natural key would be more efficient than a join on an integer - not many natural keys can be smaller than 4 bytes, and if they are, there can't be enough unique rows to make the difference material. – Aaron Bertrand Feb 3 '17 at 16:30
  • For competent, optimisable SQL I agree, but I was referring to possible limitations of SQL generators. My only experience in this area is being asked to create extensive views with which EF could be spoon-fed--though it's possible the .net devs didn't know enough about EF, or that there were other reasons. – T.H. Feb 3 '17 at 16:43
  • @AaronBertrand I would say that the only way in which they might be more efficient is if a join was not needed at all. The only places I consider the use of natural keys is with standard code lists such as ISO4127 currency codes (which are human-recognisable), and I might use GBP, EUR etc as the foreign key to an primary or alternative key on the currency code table. – David Aldridge Feb 3 '17 at 17:55
  • @David Of course, I was talking about cases where joins are necessary. There are a lot of cases where I don't want the natural key proliferated throughout all the related tables, because natural keys can change, and that is a painful thing. – Aaron Bertrand Feb 3 '17 at 18:05
  • Hmmm, I see how my answer could be misunderstood to be promoting natural foreign keys over surrogate. To be clear, I actually only mentioned them because a) I read Alexei's question as "is it a problem that we don't use natural keys?", b) Alexei's wrap-up question started with "from a DBA's perspective" and I felt I should sort of acknowledge there's more than one perspective and c) because I would think the ORM features to be used largely dictates the choice (if it actually can make a difference). I'm firmly in the surrogate foreign key camp myself. – T.H. Feb 3 '17 at 20:04
2

You have a few factors to help guide you,

  1. Definition and spec.

    If something is defined as unique by the task or the laws of physics you're wasting your time with a surrogate key.

  2. Uniqueness.

    For personal sanity, joins, and higher-level database functionality you'll need either, (a) unique column, (b) unique series of columns

    All sufficiently normalized schemas (1NF) provide one of the following. If they don't you should always create one. If you have a roster of people set to volunteer Sunday, and it includes last name and first name, you'll want to know when you have two Joe Bobs.

  3. Implementation and optimization.

    An int tends to be a small data form that is fast for comparison, and equality. Compare that with a Unicode string whose collation can be dependent on locale (location and language). Storing an 4242 in a ASCII/UTF8 string is 4 bytes. Storing it as an integer it fits in 2 bytes.

So when it comes to downsides you've got a few factors.

  1. Confusion and ambiguity.

    1. @Aaron Bertrand blog entry sums this up well. It's not self-documenting to have an OrderID by the specification and task, and then to impose an "OrderID" through the database implementation. Sometimes you have to clarify that or create a convention but this is likely to add confusion.
  2. Space.

    Integers still add space to the row. And, if you're not using them there is no purpose.

  3. Clustering.

    You can only order your data one way. If you impose a surrogate key that isn't needed, do you cluster that way or the way of the natural key?

  • Nice and short pros & cons. – Alexei Feb 9 '17 at 20:55
  • @Alexei thanks, consider marking it as chosen if it meets what you're looking for. Or, asking for clarification. – Evan Carroll Feb 9 '17 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.