Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is a choice between <a href="http://www.agiledata.org/essays/keys.html" rel="noreferrer">surrogate and natural primary keys</a>.</p> <p>IMHO always favour surrogate primary keys. Primary keys shouldn't have meaning because that meaning can change. Even country names can change and countries can come into existence and disappear, let alone products. Changing primary keys is definitely not advised, which can happen with natural keys.</p> <p>More on <a href="http://rapidapplicationdevelopment.blogspot.com/2007/08/in-case-youre-new-to-series-ive.html" rel="noreferrer">surrogate vs primary keys</a>:</p> <blockquote> <p>So surrogate keys win right? Well, let’s review and see if any of the con’s of natural key’s apply to surrogate keys:</p> <ul> <li>Con 1: Primary key size – Surrogate keys generally don't have problems with index size since they're usually a single column of type int. That's about as small as it gets.</li> <li>Con 2: Foreign key size - They don't have foreign key or foreign index size problems either for the same reason as Con 1.</li> <li>Con 3: Asthetics - Well, it’s an eye of the beholder type thing, but they certainly don’t involve writing as much code as with compound natural keys.</li> <li>Con 4 &amp; 5: Optionality &amp; Applicability – Surrogate keys have no problems with people or things not wanting to or not being able to provide the data.</li> <li>Con 6: Uniqueness - They are 100% guaranteed to be unique. That’s a relief.</li> <li>Con 7: Privacy - They have no privacy concerns should an unscrupulous person obtain them.</li> <li>Con 8: Accidental Denormalization – You can’t accidentally denormalize non-business data.</li> <li>Con 9: Cascading Updates - Surrogate keys don't change, so no worries about how to cascade them on update.</li> <li>Con 10: Varchar join speed - They're generally int's, so they're generally as fast to join over as you can get.</li> </ul> </blockquote> <p>And there's also <a href="http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vs-natural-keys-for-primary-key/" rel="noreferrer">Surrogate Keys vs Natural Keys for Primary Key?</a></p>
    singulars
    1. This table or related slice is empty.
    1. PODeciding between an artificial primary key and a natural key for a Products table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. UScletus
    1. UScletus
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODeciding between an artificial primary key and a natural key for a Products table
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 for cutting directly to the fundamental issue -- surrogate vs. natural keys.
      singulars
      1. PO
      1. USashes999
    2. COGood post, I agree but with a small caveat. I always use a surrogate except where there is an existing "standard" that is sensible to use in the context of the problem you are trying to solve. examples are currency codes - which conform to the ISO 4216 standard and can avoid the need for a join altogether e.g most of the world understands what USD,GBP,EUR mean. ISO 3166 defines Country codes. Yes these can change - but typically you would want the history in a database or audit record and ISO just deprecates the old codes e.g. TRL > TRY. Social security number on the other hand I would not use
      singulars
      1. PO
      1. USChris
 

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