Databases
StackOverflow2013
Postsdbo
POMySQL - autoincrement + compound primary key - performance & integrity
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL - autoincrement + compound primary key - performance & integrity
    primarykey
    data
    text
    <p>I have a database design that makes use of compound primary keys to ensure uniqueness and which are also foreign keys.</p> <p>These tables are then linked to other tables in the same way, so that in the end the compound key can get up to 4 or 5 columns. This led to some rather large JOINs, so I thought a simple solution would be to use an autoincrement column which is not part of the primary key but which is used as part of the primary key of other table(s).</p> <p>Here is some pseudo code showing the general layout :</p> <pre><code>CREATE TABLE Item ( id AUTO_INCREMENT, ... PRIMARY KEY (id) ) ENGINE = InnoDB; CREATE TABLE PriceCategory ( id AUTO_INCREMENT, ... PRIMARY KEY (id) ) CREATE TABLE ItemPriceCategory ( itemId, priceCategoryId, id AUTO_INCREMENT, ... UNIQUE INDEX id, PRIMARY KEY (eventId, priceCategoryId) ) CREATE TABLE ClientType ( id AUTO_INCREMENT, ... PRIMARY KEY (id) ) CREATE TABLE Price ( itemPriceCategoryId, clientTypeId, id AUTO_INCREMENT, ... UNIQUE INDEX id, PRIMARY KEY (itemPriceCategoryId, clientTypeId) ) table Purchase ( priceId, userId, amount, PRIMARY KEY (priceId, userId) ) </code></pre> <p>The names of tables have been changed to protect the innocent ;-) Also the actual layout is a little deeper in terms of references.</p> <p>So, my question is, is this a viable strategy, from a performance and data integrity point of view ? Is it better to have all keys from all the referenced tables in the <code>Purchase</code> table ?</p> <p>Thanks in advance.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USianaré
    1. USianaré
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL - autoincrement + compound primary key - performance & integrity
      1. USianaré
      1. VTFavorite
    2. VO
      singulars
      1. POMySQL - autoincrement + compound primary key - performance & integrity
      1. USuser697473
      1. VTFavorite
    3. VO
      singulars
      1. POMySQL - autoincrement + compound primary key - performance & integrity
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSimilar to: http://stackoverflow.com/questions/6372058/mysql-primary-key-in-a-relational-table-unique-id-or-multiple-unique-key/
      singulars
      1. POMySQL - autoincrement + compound primary key - performance & integrity
      1. USDenis de Bernardy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload