List:General Discussion« Previous MessageNext Message »
From:James Harvard Date:December 21 2005 11:31pm
Subject:Re: Reason for Auto-increment primary keys?
View as plain text  
I admit I too am in the habit of always defining an auto_increment primary key, but
recently gathered my courage and omitted it from a match-up table joining a table of
users to a table of categories they were allowed to use - an auto-generated primary key
would have been completely redundant. It felt quite daring though! :o

Seriously though, in my experience at least, one is usually trying to represent something
'real world' in a database schema, and usually there just isn't something that clearly
fits the requirements for a primary key (well defined in the quote Rhino gave). So very
often one just has to create an 'artificial' pk and then it makes sense to use one which
will be as efficient as possible.

In an app I'm just finishing I split the core data into many tables, but still one of
those is 40 million rows and growing fast. So I shifted everything I could out into
related tables to try and reduce the column sizes. By my calculations every byte of
storage requirement I can knock off each row means 40 MB for that table's data file
alone, and probably nearly 1 GB (more with index files?) over the whole app. That's got
to have a significant effect on performance. For example, the date (year-month) is thus
shoe-horned into a tinyint. This means that in about 12 years the client will have to
think about bumping that column to smallint, but by then I suspect the performance
implications of that will be insignificant.

Heh, 12 years ago I woudn't have worried about shaving 40 MB off a 2 GB file, because 40
MB _was_ my hard disc! So presumably as the years go by increasing processor power and
storage speed & size will mean we will no longer have to compromise on purity of db
design to get acceptable performance.

OK, now I'm just rambling.

James Harvard
Thread
Reason for Auto-increment primary keys?Rhino21 Dec
  • Re: Reason for Auto-increment primary keys?Kenneth Wagner21 Dec
    • Re: Reason for Auto-increment primary keys?SGreen21 Dec
      • Re: Reason for Auto-increment primary keys?Rhino21 Dec
        • Re: Reason for Auto-increment primary keys?SGreen22 Dec
      • Re: Reason for Auto-increment primary keys?Kenneth Wagner22 Dec
        • Re: Reason for Auto-increment primary keys?David Griffiths22 Dec
        • Are primary keys essential?James Harvard22 Dec
      • Re: Are primary keys essential?Kenneth Wagner22 Dec
        • Re: Are primary keys essential?James Harvard22 Dec
          • Re: Are primary keys essential?James Harvard22 Dec
      • Re: Are primary keys essential?Kenneth Wagner22 Dec
      • Re: Are primary keys essential?Rhino22 Dec
      • Reporting tools for summary dataC.R.Vegelin22 Dec
        • Re: Reporting tools for summary dataJames Harvard22 Dec
    • Re: Reason for Auto-increment primary keys?Josh Trutwin21 Dec
  • Re: Reason for Auto-increment primary keys?Peter Brawley21 Dec
    • Re: Reason for Auto-increment primary keys?James Harvard22 Dec
  • Re: Reason for Auto-increment primary keys?Rudy Lippan21 Dec
RE: Are primary keys essential?SST - Adelaide)22 Dec