1

I need to create a database design where bar or event owners (admin-table because they own a profile) send alerts to users.

I want to create an "ALERT" table but have a hard time deciding what primary key to use. I thought adding a composite key containing at least admin_ID (PFK), user_ID (PFK) and I thought to add Date and Time stamp to the primary key to indicate that many alerts (notifications) can be sent by an admin, but only 1 at a certain point in time.
However, from this thread: Timestamp as part of composite primary key?, I learned that I shouldn't use the timestamp.
By the way, it has not been decided what DB software we will use at this point.

I sometimes have the tendency to quickly move to an autoincrement key. I have never noticed problems with that (in Access), however from what I read, this may not always be the most meaningful thing to do, therefore I ask the question to professionals.
I only have one chance to do this the right way to make the back end fundamentally right.
What are your thoughts on this?

Cœur
  • 37,241
  • 25
  • 195
  • 267
html_programmer
  • 18,126
  • 18
  • 85
  • 158
  • 2
    A surrogate auto-increment column is never meant to be "meaningful" - it's supposed to provide a guaranteed uniqueness, and an efficient way of identifying and finding rows of data in a large data set. Compound primary keys make your life harder - all references from child tables and all joins between those tables will always need *all* the columns of the compound PK - which can get quite messy rather quickly. I don't see anything wrong with using an auto-increment `ID` column - other than it introduces a tiny bit of extra "non-productive" data into your model – marc_s Sep 12 '12 at 12:05

3 Answers3

8

Your going to get a lot of heated debate over the issue of what to use for a PK. A purist will tell you that you should never use an auto-increment key (assuming SQL SERVER). Someone who's been in the real trenches will tell you they are perfectly fine as a PK (in most cases) and have some real advantages.

I won't try to persuade you one way or the other. But I will tell you my experience. I have been developing software for 25 years and have been using an RDBMS for much of that time (mostly SQL Server). Personally, I find auto-increment PKs invaluable and 99.99% of the time would never consider using anything else. Why?

  1. SQL Server generates them and, therefore, handles all concurrency issues.
  2. They are small in size and, therefore, lookups on these keys are very fast.
  3. You can easily refer to a particular row in a table by its Id value. This may not sound like a big deal, but it can sure come in handy. For example, I cannot tell you how many times I've asked a co-developer to take a look at a row in a table with key value 12345. A simple thing, but very useful.
  4. FKs that reference a PK in a table will be the same type, and, therefore, also small and fast, and easy to work with.
  5. Little or no fragmentation of the index, especially if the PK is a clustered index (in SQL Server).

There can be one big disadvantage with this sort of PK. If you ever have to merge rows from one database to another, you have the potential of running into PK value collisions. However, there are ways to work around this as well. Also, of course, an auto-increment value will have no true meaning. But that's ok. Its purpose is to provide uniqueness.

Is this a perfect solution. Nope. And others will disagree with the use of them. However, they have worked very well for me, for most high-end, and business critical, projects.

Randy Minder
  • 47,200
  • 49
  • 204
  • 358
  • So it would be okay if I use in this particular case an autoincrement PK, and Admin and User can be regular FK's? I would be very glad with this solution! – html_programmer Sep 12 '12 at 12:07
  • +1 couldn't have said it any better - a "real-world" opinion that'll work and that doesn't cause any grief in the long run! – marc_s Sep 12 '12 at 12:08
  • I'm thankful for your reply + @marc comment, how about this thread: http://stackoverflow.com/questions/1997358/pros-and-cons-of-autoincrement-keys-on-every-table => this is what confused me. I would otherwise also choose for your solution all the time. – html_programmer Sep 12 '12 at 12:10
  • 1
    @KimGysen - Like I said, you are going to have differing opinions on this issue. I'm a guy who's worked in the trenches, on mission critical apps, for years, and have almost never regretted using auto-increment PK values. – Randy Minder Sep 12 '12 at 12:14
  • 1
    @KimGysen: surrogate PK do have downsides - as pointed out in that SO question you're linking to. But in my opinion, those are *miniscule* compared to the problems of compound and/or changing primary keys. All those downsides can easily be addressed (unique constraints on columns that need to be unique etc.) – marc_s Sep 12 '12 at 12:15
  • Understood, then for me experience beats theory. Hm, although also in theory it seems to be the best option like Marc indicates. – html_programmer Sep 12 '12 at 12:16
  • **If** surrogate keys are redundant (because a natural key is available), they *only* have downsides. – wildplasser Sep 12 '12 at 12:30
  • @wildplasser: agreed - but a really good natural key (narrow, unique, static) is **hardly ever** available in my experience of 25+ years... – marc_s Sep 12 '12 at 13:09
  • Well, as a contra-example: consider an EAV-model. The value-table would have a *natural* PK of {entity_id, attribute_id}, both would be not-nullable and FKs to the entity and attribute tables. A tuple with any of them null or outside their FK domains would make no sense (a row like that would not have any meaning and should not be present); and therefore is forbidden. Adding a surrogate key would only add a constraint (which is never violated and thus is redundant) – wildplasser Sep 12 '12 at 13:21
  • NAd of course, you should still put a unique index on the fields taht would have composed the coumpound key, this will preserve teh uniqeness while still allowing you to use the integer field to join on and for easy refernce and to prevent having to change child records when teh values in the compopound key change. If you use autogenerated keys and you have a natural key whether singular or compound, you still need the unique index on it. – HLGEM Sep 12 '12 at 13:54
  • Guys, if I use an autoincrement as unique ID, how can I know for sure that a combination of admin and user is not introduced double? I could create the restriction through the front end but I have this nasty feeling that this is wrong. If I want to make the restriction on the back end, should I add the FK's to the primary key after all...? – html_programmer Sep 13 '12 at 11:49
  • @KimGysen - I would create a unique constraint and/or key on Admin + User. This is completely separate from the PK and will prevent duplicates. – Randy Minder Sep 13 '12 at 16:49
  • Thanks Randy I think I'll follow that advice. I asked the question in a new post: http://stackoverflow.com/questions/12406870/auto-increment-vs-composite-key/12416635#12416635 I decided to go for the increment + Unique constraint on the foreign keys as you mentioned it. In general, I feel that this is the smoothest and yet safe way to do it. However, in the other reply, most replies are contradicting this one. – html_programmer Sep 14 '12 at 07:36
  • There are two reasons to declare a PK. Indexing and constraining. If it is important to detect and prevent double entries in the ALERT table, and if a surrogate key is used, this will have to be done in the application. – Walter Mitty Aug 03 '18 at 08:18
0

you have always the option to use UUID ( http://en.wikipedia.org/wiki/Universally_unique_identifier ) for your database primary keys.

All major programming languages have a support for it and IMO it is one choice, probably though not the best because it suffers from low efficiency .

MaVRoSCy
  • 17,747
  • 15
  • 82
  • 125
  • 1
    But for certain systems - like SQL Server - having a GUID as the primary key (which automatically also is the clustering key) has a horrible negative effect on the index fragmentation and thus on performance. Also: not all database systems have a GUID datatype ... – marc_s Sep 12 '12 at 12:09
  • yes, i know that there are some fallbacks in this approach... Its something the user should consider also. I will rephrase my answer. Thanks – MaVRoSCy Sep 12 '12 at 12:14
0
  • There is nothing wrong with composite primary keys per se
  • There is a problem if any of a PK's components is NULLable
  • ... such as when any of the components also functions as a FK to another table (or "domain")
wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • 1
    _"There is a problem if any of a PK's components is NULLable"_ Which is why no DBMS will allow NULLs in PKs. – Branko Dimitrijevic Sep 12 '12 at 12:30
  • For good reasons. The next question would be: "why should a FK be (NOT) NULLable?" . IIRC, Chris Date has written a lot on this subject. – wildplasser Sep 12 '12 at 12:35
  • 1
    When a child endpoint of a FK contains NULL(s), a "MATCH SIMPLE" FK will not be enforced at all. A "MATCH PARTIAL" and "MATCH FULL" may behave differently on composite FKs, but most DBMSes only implement "MATCH SIMPLE". – Branko Dimitrijevic Sep 12 '12 at 14:08