Serial number with leading zeros?

[xochi]

I have two tables. One has an auto-enter serial number that is a number, but I entered 00000 in the serial number box, so the serials all have leading zeros -- 00001, 00002, 00003 etc.

Another table is related to this table, and I store these values as foreign keys. When I do this (using Set Field), the leading zeros are stripped, so I end up with 1, 2, 3...

However, when I first set up the solution, I updated the old data using Replace Field Contents, which kept the leading zeros.

As far as I can tell, since both fields are numeric, the relationship works, but having a mix of foreign keys with and without leading zeros worries me.

Should I care?

[Mike Williamson]

I never use leading zeros or text strings in my primary keys. Period, no exceptions. I have seen these fail too many times. I know some developer do this, but I strongly protest.

Use simple numbers as auto-enter serial for your primary keys and you won't get burned. Ever.

[Lee Smith]

Change the fields to Text.

[Lee Smith]

I never use leading zeros or text strings in my primary keys. Period, no exceptions. I have seen these fail too many times.

This is the first time that I have heard that padding the serial numbers with leading zeros would cause problems. Could you give us an example of what happens?

Lee

[Søren Dyhr]

The padding will set an upper limit to when the uniqueness is jeopardized ... and since these are supposed to work as relational keys, is it hard to say why such thing should be needed at all?

We have to remember that unstored calc's values only holds values when showing up via a layout rendering, why not use this virtue instead if the display of a relational key if it ever should have any relevance for the user of a solution???

--sd

[xochi]

It seems as if numbers with padding, e.g. "0000034" are converted to a plain number (34) when used in a relationship key, as long as the Field is defined as a number. I wasn't 100% sure this was always the case, however, and wanted to know if anyone knew for sure whether this was reliable.

I'm using the foreign key as an intentional denormalization/ performance hack in a 1 million+ row match where doing it the normalized way is way too slow...

[comment]

converted to a plain number (34) when used in a relationship key, as long as the Field is defined as a number

I believe it is converted to number when the OPPOSITE matchfield is of type Number. Which means you can expect problems if you'll ever need to match a calculated multi-line.

[xochi]

I believe it is converted to number when the OPPOSITE matchfield is of type Number. Which means you can expect problems if you'll ever need to match a calculated multi-line.

Thank you -- that's a good example to keep in mind.

On a slightly different topic: I'm using number fields (instead of text fields) trying to squeeze the maximum performance out of filemaker. I'm assuming that text fields are slower (and take more KB) to index than number fields. Anyone know if that's a valid assumption and if so by how much? If there's not much difference, then I'd probably change my strategy and use Text based serial #s of the form TN-0000000 where TN is a 2 letter abbreviation for the table name. I find that this helps me keep track of which foreign keys go with which foreign table more easily.

[mr_vodka]

http://www.fmforums.com/forum/showpost.php?post/251668/