Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been working with a group who have built a system where they've coined a few new terms in the process, and I wanted to know if there's something more standardized they should be calling it.

Basically, they have tables which hold versioned info, so the unique key for the table is the identifier for the document being tracked, plus the record number (although, the record number is auto-numbered, so it's unique in itself). They refer to the fields that make up the document's identifier (it changes for each table), as the 'prime key' ... but the fields both individually and collectively (eg, if it has fields A, B and C to get a unique identifier, they might interchangable refer to A as being 'a prime key' and A/B/C as being 'the prime key'

If they had a normalized database, this would be an obvious candidate for a foreign key -- but it's not normalized, so it's not a reference to a different table.

Is there some name for this concept -- an identifier to select some concept that's not what the table is based on, but which isn't a foreign key?


update: it's not an alternate key, as it doesn't identify a unique record within the table; it identifies a group of records. Take the folowing table:

id   document_id   version_no
--   -----------   ----------
 1             1            1
 2             2            1
 3             1            2
 4             2            2
 5             2            3

So we're tracking metadata for 5 objects, which are two different files, one of which has been updated once (2 versions/editions of it), and one which has been updated twice.

We have the following:

  • candidate keys : (id) or (document_id, version_no)
  • natural key : (document_id, version_no)
  • surrogate key : (id)
  • primary key : (id, document_id) (I didn't select it)

... if document_id were a relationship to another table, it'd be a foreign key, but 'foreign key' typically also infers a constraint (ie, the value has to exist in some other table before it can be used here) ... but in this case, it's not. So, is there some other term to describe this as something that selects a related grouping of objects rather than a single record out of the table?

The first definition of surrogate on the wikipedia page for surrogate key fits, but that's not the common usage of the term, where most people consider a surrogate key to fit the second definition:

  • Surrogate (1) : This definition is based on that given by Hall, Owlett and Todd (1976). Here a surrogate represents an entity in the outside world. The surrogate is internally generated by the system but is nevertheless visible to the user or application.
  • Surrogate (2) : This definition is based on that given by Wieringa and De Jonge (1991). Here a surrogate represents an object in the database itself. The surrogate is internally generated by the system and is invisible to the user or application.

update 2 : Basically, if you had a table that handled versioning of records, if you moved the records to a new table and stored editing history, the fields that link back to the original table would be considered a foreign key ... but if you stored all of it in one table, without relating to a secondary table, is there a name for the group of fields that identify the multiple versions of the record?

The reason I ask is that the group I'm working with has decided to call it a 'prime key', and when I came on board, I reviewed all of their documentation, and told them they had a typo, and it was the 'primary key', but they explained that no, it was the phrase they had coined to describe this concept.

I'm trying to identify if there's a accepted name in the database community for this concept.

share|improve this question
    
In some ways the document_id is a surrogate key, but in our particular case, it's the hashing of a natural key (time placed into 12 second 'slots' ... the natural key (time) may change between versions/editions. (it's science data, so there are corrections for clock drift, etc.) –  Joe Jan 11 '11 at 16:07
    
If id is a candidate key then (id, document_id) can't possibly be a primary key because it isn't a minimal superkey. It might be the column referred to by a PRIMARY KEY constraint, but that's different. –  sqlvogel Apr 13 '11 at 10:06
    
@dportas : it's not a candidate key ... it's technically an identifier into a different concept. Let me go an update the question. –  Joe Apr 13 '11 at 14:01

3 Answers 3

User terms surrogate key and natural key for primary key as a variant.

Surrogate Key:

Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table. Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).

Natural Key:

Keys are natural if the attribute it represents is used for identification independently of the database schema. What this basically means is that the keys are natural if people use them example: Invoice-Numbers, Tax-Ids, SSN etc.

Surrogate Keys vs Natural Keys for Primary Key

share|improve this answer

A "candidate key" is any column or collection of columns that CAN uniquely identify a record in a table.

A "primary key" is a candidate key that is defined as the primary key for the table.

Internally, a primary key is simply a unique index, clustered or non-clustered, that is typically used in foreign key relationships.

There is more to it than this, as always, but I hope this helps.

share|improve this answer
    
But it's not a candidate key ... it identifies a group of records .. let me go clarify the question. –  Joe Jan 11 '11 at 15:21
2  
I apologize for the confusion. In this case, the record number is a "simple key", because it CAN uniquely identify the record, and the combination of the document identifier and the record number is a "composite key". The difference between a compound and composite key is that each column in a compound key is a simple key, whereas at least one key in a composite key is not a simple key. en.wikipedia.org/wiki/Compound_key –  Matt M Jan 11 '11 at 15:35
    
when quoting, feel free to make use of the backtick surrounding element to differentiate text you've orphaned from an article, or wrap it in braces with the URL after in parens so that we know where the text came from ~ Just my unwanted $0.05 –  jcolebrand Jan 11 '11 at 16:12

A compound key would also define what you're describing.

I've heard it called other things as well, like composite key. But note that the composite key is not the same as a compound key. Just trying to alert you to two vocabulary words.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.