Serial Numbers: Text or Numeric?

[K1200]

Is there a database size consideration when deciding whether to use Text or Numeric auto-enter serial numbers?

In designing a database that, potentially, could contain hundreds of thousands of records in each of several tables it occurs to me that I could be wasting a lot of space using Text IDs for those certain tables. "Old school thinking" leads me toward numeric (e.g., 1 to 999,999) instead of text (e.g., A000001 to A999999), even though I prefer to use Text if the ID is ever displayed to the user. But is that reasoning valid or does FMP store Text IDs in a compressed form that makes space requirements a non-issue? (For readablity, I even have a few IDs like TEST0001, TEST0002, etc.)

A RELATED QUESTION: does FMP offer protection against a Text ID wrapping around (e.g., A999999 > A000000)?

A megabyte here ... a megabyte there ... it all adds up!

[John Mark Osborne]

There is a thread already on text versus number at:

http://fmforums.com/forum/showtopic.php?tid/176729/post/205586/hl//#205586

If you use a serial number as a text field and include leading characters to identify the table and leading zeros to make the numbers sort as numbers in a text field, FileMaker will add a digit if you didn't include enough spaces. For example:

A01

A02

A03

.

.

.

A99

A100

[Søren Dyhr]

ID's should never be shown to the user ...but to your question, I don't know if there is a penalty to pay for using text indexing ...a hunch tells the there is!

However have I switched completely away from text, because when writing a CF or a repeating calc'field to make a multiline key, makes it a whole lot easier to debug if the type is kept as number only.

For readablity, I even have a few IDs like TEST0001, TEST0002, etc.

This can be done Mbyte thriftily different, the field you inspec can be styled as currency, where you enter the word "test" instead of $ or € or £ although you can't make leading zeros.

--sd

[Genx]

ID's should never be shown to the user? I'd disagree with that in certain circumstances soren e.g. multiple transactions, one needs to be isolated, you can only really do it by searching an id.

[Søren Dyhr]

But can't you make seaches in invisible fields, I can, please note that the actual ID intended for relations, has nothing to do with invoice numbers or similar human readables...

With ability disguise the ID in popups, have we been better equipped for this distinction.

--sd

[Genx]

Well you could always just disable ability to edit the particular field, it doesn't harm the solution, though i must admit it does sometimes look ugly so i try to refrain from doing it.

I don't see why an id can't be human readable, it's not like i'm using a binary string 200 in length to identify my records in most cases you might aswell use the same field, it doesn't really make a difference i mean how is 2042121 any different to INV-042121 any different to Invoice-042121 - either way the prefix is likely given according to where they are searching from so all they're search is for the number. If you have a simple numerical primary key why go out of your way to creat a "human readable" one which probably wouldn't be much different if you wanted to guarrantee uniqueness.

~Genx

[Oldfogey]

Well you could always just disable ability to edit the particular field, it doesn't harm the solution, though i must admit it does sometimes look ugly so i try to refrain from doing it.

You are joking, aren't you? Give the user the ability to edit the key field!?!?!?

I am extremely biassed on this issue. I have a relatively simple DB in which I used Text keys. These have caused me so much grief, I even considered doing a completed re-design for free. The problems were mostly my errors but errors which wouldn't have occurred with numeric keys.

[Vaughan]

To throw in my 2c...

I use "numeric" key values ( single digits, no leading zeros or alpha characters) but store them in TEXT fields. I developed this practice in FMP 6 when creating multi-key fields and value lists; text fields are easier to work with than numbers.

[Genx]

You are joking, aren't you? Give the user the ability to edit the key field!?!?!?

... Where does it say that i'm giving the ability to edit the key field.. i said disable editing if you display it?

[Søren Dyhr]

I developed this practice in FMP 6 when creating multi-key fields and value lists; text fields are easier to work with than numbers.

That's correct, but it takes only

&""

...to enforce a typecast, when you throw it back again:

Filter($variable;"0123456789")

--sd

[Oldfogey]

Ah, the joys of no face to face contact, no pauses, etc.

Obvious misunderstanding.

You did say "disable ...." but added "....though i must admit it does sometimes look ugly so i try to refrain from doing it."

(Must admit that I didn't really think you were that mad.)

Edited June 25, 2006 by Guest

[Genx]

Vaughan, why do you store as text?

[Søren Dyhr]

Vaughan, why do you store as text?

He said to avoid type mismatch when using multilinekeys...

I have been doing similar, but are vaugly beginning to think it's a personal neglection or ignorance of the the transaction model. It's a so Filemaker'ish approach to address say bookings - mostly because import between tables in IWP are grayed out....

--sd

[Genx]

ah, rightio

[Vaughan]

I adopted this in FMP 6 days... Create a value list to pull in related keys, then use ValueListItems() to put the related keys in a calculation field, base a relationship on the calc field.

I found that text worked better and more reliably than number.

Much of this is redundant, or soon will be.