Use Auto-Enter Serialno or name in relation ?

[Normand]

I have a general FM relationship question:

I have a one-to-many relationship between my table City (one) and table Contacts (many) so that I can select the city from a drop-down list when entering the address in the Contacts layout.

I need your opinions about if the relationship should be using a Auto-Enter serial no field assigned to each City, or simply the City name ?

I think, in a general view of database relationships (and please correct me if I'm wrong), that it would normally be considered more "efficient" to store in the Contact record the SerialNo field of the City (or any sort of unique and short value allowing to identify a unique record), instead of the full name, in order to set the relationship.

In Filemaker, does this apply ? Does it make a big difference ? Or should I just use the City Name field as the key for the relationship. I'm wondering this about many other relationships (Client -> Contacts), (Company -> SalesPersons), etc.

Thanks for your input.

[Inky Phil]

For me, in the case of a city I would relate on the city name but in the case of a company for instance I would relate on ID.

Why the difference? Because a city is not likely to change it's name whereas a company might well do. That way you can change the company's name and still relate to all it's contacts etc.

It will be interesting to see what others recommend

Phil

[T-Square]

Phil--

I'd actually go a different way and relate on an ID field. While it is true a City won't often change its name (although there is the story of the town in New Mexico that changed its name to "Truth or Consequences"...), using an ID gives other benefits. For example, you could change your City table to be a ZIP table; each entry there has City State and ZIP. You put in the ZIP, and the City/State are automatically captured. Users only select one field, cutting down on error.

[As an side, many assume that they can use the Zip code as the unique ID, but there are instances where a Zip code crosses jurisdictions, so you would be well advised to use a serial number anyhow. I live in such a split zip code, and I repeatedly get mail (eventually, that is) that uses the dominant city designation...]

Now, the down side on this is that you end up with a large table of Zip code entries. Really big, if you go wild and get as many states as you can. The upside is that once you set this up, you can use it over and over again.

David