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)
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.