Unique ID as Text or Number

[JeanWM]

As far as I can see:

Numeric Serial ID Fields:
Allows for more accurate sorting --- depends what you call 'accurate'
Can be used in > or < type calculation relationships. --- so can text as long as you understand what is being compared
Does not allow for multi-line entries (multi-line keys, etc) in Relationships. --- but you can coerce a multi-line text at one end to relate to a single line number at the other

Text Serial ID Fields:
Allows for Alpha-Numeric keys. --- you can also do that in a number field
Allows multi-line entries (multi-line keys, etc) in relationships. --- see above
Can use alpha characters to identify table or file of the ID (ORD000001, is from the Order table, etc). --- see above
Needs to be padded with prefixed 0's to allow for correct sorting (or can use a calculated field to GetAsNumber). --- true

One big difference between text and number fields is that the index of a number field is half the size of that of a text field. That may be a consideration for very large record counts.

This may not seem very helpful (but it is only the start of the discussion) - the "possible issues or problems of using a text field as a unique serial ID" depend on what you are going to be using it for.

Can I have some insight from the forum members?
_________________
JeanWM - Moderator
Dar síntomas erróneos, resulta en soluciónes erróneas.

[alf-admin]

[JeanWM]

Thanks for your 2 cents Alf.

The discussion we had is which type of field to use for the unique identifier of a record or column.

Text or number.

In an application with several tables, should we use a reference to the given table? And why (not).

Or do we have to use a more complicated data structure and use a sort of calculated result.

And how about the label of the field:
should we incorporate a
'p': for primary identifier
'f' : for foreign identifier
'a' : for alternate identifier
'c' : for compound/calculated/concatenated identifier
'm' : for multi-line identifier

As you can see we enlarge a little the concept of 'key' to 'identifier'.

An example could be the identifier of an invoice.
Something like: INV yyy, where INV stands for 'invoice' and yyy a given number construction, which could hold the 'year' and a serial incremented number.
_________________
JeanWM - Moderator
Dar síntomas erróneos, resulta en soluciónes erróneas.

[alf-admin]

[peter]

Jean,

basically it doesn't matter what kind of key you use as long as it is unique. And a serial value should do the job, whether it is numeric or alpha-numeric.

From a data modelling resp. the user's perspective it might be useful to use a (more or less) natural key as your invoice number. Why to introduce an additional ID column if you still have a unique one? And modern database systems can handle alpha-numeric values well (if your tables haven't millions of rows).

I agree with Alf that the serial no. in your INV example is sufficient. But if it gives more meaning to the user if you include INV and the year, why not. I know, this could be a basis for a long debate about avoiding redundancy ...

@Alf: Jean's invoice number scheme is unique over the years, even if it starts counting every year.

From a performance perspective I suggest a numeric value. E.g. for OLAP systems with billions of rows an alpha-numeric key wouldn't be a good idea. String operations are definitely slower than e.g. an integer comparison.

But lately it is also a matter of the amount of working memory (RAM). If your system runs well with alpha-numeric keys and the difference to numeric keys are only some milliseconds then it really doesn't matter. In this case the user requirements should be the decisive factor.

Back to your example: of course you could concatenate the INV and the year to the serial no. in the application only. But to split semantically connected information between database and application is not a good idea, because you have to implement this connection in every application based on the same database. And the semantics can only be recovered if you use the database AND the corresponding application. This violates Codd's integrity independence rule.

The question how to label a field is a matter of taste. E.g. some modelling software generate foreign keys with a "FK_" prefix. If it helps in application programming, why not. But to be honest I never used such prefixes, because a database application developer knows resp. has to know its data model. So he/she knows if an identifier is primary, foreign, compound, etc.
_________________
Peter
Moderator

[alf-admin]

Jean,

Peter is right to the point. Let me complement with a few viewpoints, because the decision on primary keys is a very important one. Also, though I hate that part, due to performance sometimes (I don't like cluttering logic with performance considerations only because of the current physical state of computer performance: Not logical. But I understand I can be taken for a purist, which I am, I guess). Peter has very valid points around the issue.

But in the logical DB design phase, the only thing that is important, is to identify candidate keys. My best experience is when I can identify a natural, single attribute candidate that holds as a primary. Why?

1. A natural key has a natural place in the tuple (row). Very good.
2. A single attribute as key will ensure that your relation is up in the 5th Normal Form directly, provided it is already in 3rd Normal Form: 4th and 5th Normal Forms only deal with the problems of composite primary keys. Great design.

That being said: If you cannot find a single-attribute natural primary key and therefore proceed to assign a sequence, then normalisation must be executed with special care, as it (the sequence) is only introduced because you miss that natural, and dependencies must be vigorously examined. Hope this makes sense, if not, give me a hint.

Invoice numbers are the kind of attributes that can well be assigned by a sequence. Like Order no's etc. It's just a number, with the sole purpose of uniquely identifying something. A manually assigned serial no. did the trick in the old, pre-computer days: Nothing's changed.

[JeanWM]

@ Alf & Peter

Thank you both for the insight.

It's good to have some ideas 'outside' the normal rules.

For Alf: most of my students speak Spanish and have little knowledge of English.

They read the information on your site, but to have them write something on the forum....
They prefer me to 'translate' and post.
Anyway, we're working on it....

You both know perhaps that our core engine is not SQL but FileMaker Pro.
Some details are a little different.

In FM we use keys to build relationships, but we can also use 'identifiers' for a bunch of other things, like scripting.
Another FM specific engine.

And we do not show keys to users, but sometimes we need a meaningful identifier to show to the user, hence the INV...

Can the identifier be the same as the key?
Now that is again open for discussion.
A yes can agree with redundancy....
A no can trigger the 'why not'?

@Peter

[peter]

3 weeks later ... sorry for the delay ... audit period

[JeanWM]