An alternative to ID in database schemas

toma42

For the past 13 years I've used a schema design which works well and never uses a column named 'id'. This article describes it and I would like feedback from the community on my approach and the article itself.

http://www.stuki.net/2010-12-08-an-alternative-to-id-in-database-schemas.52259.html

Thanks,
Tom Anderson

bradgrafelman

So you use the same concept as the "id" column, but you just call it something else?

toma42

bradgrafelman;10970051 wrote:
So you use the same concept as the "id" column, but you just call it something else?

Come on, no baiting.

There are many specific concepts used which are used elsewhere but for this approach it's a specific set of rules. id has no analog to ref and I've never seen xref used outside this approach (not my approach...I learned it 14 years ago).

bradgrafelman

I guess what I mean to say is that it just seems like another set of naming standards where you use "key" instead of "id".

In a previous company I worked for, the naming scheme was much the same (e.g. "_xref" table suffixes, etc.) but the identifier columns were still called "id".

For me personally, it still makes sense to keep it as "id" rather than "key", because I think of the "id" being the value of the "key" (or one of the keys) in that table. In other words, "id" is the value, "key" refers to the SQL relationship.

toma42

But what about foreign key naming? Is it addressed as precisely as this approach?

I haven't created an id based schema but I have used them and I've never seen an approach I felt would allow you to see all foreign keys in a table as easily as a ref_ prefix.

On another note, the column naming standards were lifted from Magento...perhaps it needs attribution.

toma42

Isn't the key vs id use as sql vs value analogous to $tableName in code and table_name in the database? There are interpreters in ORM tools for exactly this naming discrepancy. I prefer to use a concrete dialect (key is a key) rather than this translation of an id is a key.

(this feedback is appreciated and I'll edit my article based on the discussion, of course)