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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
