Primary Keys

[m3nme]

I am a long time Filemaker Advanced User, and have learned all I know by just trial and error. I have become sort of a power user, but I am constantly seeing work by people like you that make me realize I have a lot to learn.

So, I recently bought the Filemaker Training course that is being offered. I am finding that it has several good tips and techniques that I have not known about,, but they keep referring to a technique/strategy that I just don't get.

I am talking about the use of primary keys and foreign keys. I do understand those, but the way they use them is what is stumping me. I have always made a primary key something that is understood by the user, but they strongly recommend that one use serial numbers for the keys. This does not make sense to me because it would require the user to know a numerical key to enter data instead of a familiar piece of data such as is already required to be unique in the table. I do understand that a number is probably better for an employee like a clock number because it is possible for two or more people to have the same name, but should the primary keys always be set up such that the data entry person has to know or look up a number to make the relationship instead of just making the unique field the primary key?

[Fenton]

This is an interesting dilemma that we all face to some extent. You are likely reacting to "blanket" statements developers tend to make, usually in an effort to simplify concepts for beginners, which sometimes backfire.

There is another thing you have to take into account, which is the option to [x] Show only values from the 2nd field, in the Value List dialog. This allows you to have it both ways, use an auto-entered serial id, while showing the user only the value they expect to see. Mind you, that 2nd value MUST be unique, and should be validated as such, or you'll be in trouble.

It can be presented as a drop-down list or a pop-up menu, the latter being safer, though you can also validate the choice to be a member of the value list.

If you have a relatively short value, which you present as a pop-up menu or which you validate to be a member of the value list, and which will NEVER change, then I'd say go ahead.

But if change is a possibility, no matter how slim, then that will be a problem. That is usually not within your control. In most cases it is not worth the risk. The hidden serial id field is just safer. It also gives you a fall-back if something goes wrong with an operation that changes the data field.

Edited April 21, 2009 by Guest

[m3nme]

Fenton,

Thanks for the reply and suggestions. It makes sense. I think the main thing is that you have cleared up my worry.

I will probably try to use your suggestion about using the second field values. I have used that many times, though not for this purpose.

This has never been a problem for me before, but I can see now that it can be good practice.

thanks for the reply and advice.