Primary Keys

[agtjazz]

I am reading two different beliefs when it comes to primary keys (unique auto-enter serial numbers).... one recommendation is to keep the field as number- for fast indexing. The other recommendation is to keep it as text and begin the serial number with a prefix that will indicate what table it is in.

What is the best practice for Filemaker? What is normally done? I have 10 tables in my database- and this is the first iteration. In the next revisions, more modules (so more tables) will be added.

If I keep the primary keys number fields… is there a script or something that will help me keep track of the last serial number used for each table? Or the range? When I add a new table, I need to check to make sure it’s a new range to prevent overlapping.

Thanks in advance

[comment]

I can think of a few rare examples where a prefix might be useful. I believe I can also think of way to make those work without a prefix.

I see no need to prevent "overlapping" of serial numbers in different tables: let's say Customers go from 1 to 845, Invoices go from 1 to 10594 and Products go from 1 to 329 - so what?

[John Mark Osborne]

I create serial numbers as text fields for two reasons:

1. It allows me to add a prefix which helps me identify the table where the serial number came from no matter where it is. Often, I use serial numbers or lists of serial numbers in fields other than foreign keys and this can really help identify them.

2. I don't use multi-keys (return-separated list of primary keys) as much as I did with earlier versions of FileMaker but they can still be helpful. In this case, your multi-key is stored in a text or global text field and should relate to a serial number of text type also.

I don't think there is one right way of defining serial numbers. I'm sure there will be many more opinions posted here. Read them all and pick the best combination of ideas for your development style.

[Genx]

Ive found number serial fields arent a good idea because they tend to play up, especially if you try to do standard searches on them.

[comment]

None of the above.

EDIT:

I see you have deleted one. Now test the other one, too.

Edited May 13, 2006 by Guest

[Genx]

Yeh, i realized my argument had no weight there, but i swear it used to play up, oh well. One thing alpha keys do let you do however is direct users to correct tables.

I.e. lets say you have a to do list that relates to specific contacts and .. contacts2 people (i cant think of a better name for a second group of people)

You could do something potentially powerful as in direct the user through a portal.

i.e.

Main portal showing all to do tasks by date

Option 1: If Prefix starts with c go to related record through relationship X to Contacts Layout tacked on to contacts table.

Option 2: If Prefix starts with cc go to related record through relationship Y to Contacts2 Layout tacked on to contacts2 table.

I can't articulate myself very well today for some reason so if ive not made myself clear... ill try again later..

Cheers,

~Genx

[comment]

I can think of a better example (said so in my first post), but it is rather esoteric, and can be made to work with numbers too (said so in my first post, too).

If we're making a list, then let's also mention:

1. Text IDs, in a default search: searching for "1" will also find "10" and "11" (i.e. exactly the opposite of what you said). Easily solved, though, by searching for "=1".

2. Text IDs will not sort correctly, unless padded to fixed length - which puts a limit on the number of records permitted. Sort by ID is rarely required, though.

[Genx]

Okay your right, i quit. I've got an accounting assignment to do anyway *shudders*.

I'll have a proper look into this next week so i don't make silly mistakes - especially when posting - again.

Though i do employ that to do list of mine over 12 tables and seems to be working successfully and quickly so far.

Cheers,

~Genx

[Genx]

Out of curiosity.. if i do shift my thinking.. how do you identify a table through numerical serial?

Cheers,

~Genx

[comment]

Why would it be necessary to identify the table?

[Genx]

...incase i needed to? As with the to-do list example above (a real example), im not sure how i would be able to achieve it without being able to identify the table it originated from.

Edited May 14, 2006 by Guest

[Fenton]

Another alternative to prefixes on the IDs, in a shared table like ToDo's, is to let each of the master tables have their own foreign key field within the ToDo table. Yes, it's more fields, but then it no longer depends on the prefix. Perhaps less flexible however.

[comment]

Fenton stole my first sentence: each table should have its own foreign key within another table. Now let's say there are many tables, and there is a central "log" type table, where each record, in any table, has an entry (sort of like a ledger). Here a prefix COULD be useful. That is the "rare situation" I meant above.

But a combination of two keys - SerialID and Type - can work here just as well, if not better: no calculation is required to extract the type of record from the prefix, so there's less room for error, and no breaking of the "one fact per field" rule.

[Genx]

I'm following the theory half of all this..

Ah, i think i might understand. So your suggesting two sets of fields on both ends? One as foreignID as a type field --> global i guess. One as an actual primary key?

I actually set this up but never utilized the relationship lol. God knows why, but my type identifiers still exist but i'm not going to implement this into my current project out of fear of breaking relationships.

Anyway, good thinking guys, sorry my head's a bit thick of late for some reason.

Number based serial and type fields it is!

~Genx

[Genx]

Just to clear up finally, We're suggesting:

Type(Global) = TypeForeign(text)

Primary(NumSerial) = Foreign(Num)

.. Or so i hope

~Genx

[comment]

Basically, yes. The Type field on the left side could be unstored calc. But if you ever want to GTRR in the opposite direction, then it needs to be indexable - so either a stored constant calc, or an auto-enter text field. It's the same as a prefix, just broken down to pairs.

[Genx]

Hmmm, Awesome. I enjoy it when things make sense, but i really like this logic. Cheers,

~Genx