Hi,

I'm building a new database and I've it set up the primary key fields of my tables in a way that I think is effective, but I want to check with the forum before I began the next stage of testing/development.

I've created a schema below that shows a part of my database and the way that I have my primary field concatenation calculations defined. They are all fields that are populated via a calculation set to replace existing values. All are also required to be unique. Is this an acceptable way to create compound keys? Should I have all of the columns from each of the "previous" tables in each of the later tables instead of just making the foreign key part of the calculation? Given that I already have the database set up this way I'd prefer to keep it as is, but if there's a good reason to change it I'd rather do it now before I actually begin entering data. Given the information quoted below I wonder if I should be using multi-criteria relationships instead (since it appears that my current technique is outdated)? ...any advice is appreciated

While searching the forums, I found this advice:

Now I see (hopefully). There are two methods to accomplish this:

1. In the Appointments table, define a text field, set it to auto-enter a calculated value = CustID & "|" & GetAsNumber ( AppointmentDate ) & "|" & AppointmentTime

Uncheck the "Do not replace existing value.." option. Set the field's validation to 'Unique value'.

2. Define a relationship from the Appointments table to another occurence of the same table (self-join), let's call it Duplicate:

Appointments::CustID = Duplicate::CustID

AND

Appointments::AppointmentDate = Duplicate::AppointmentDate

AND

Appointments::AppointmentTime = Duplicate::AppointmentTime

Validate all three fields by calculation = IsEmpty ( Duplicate::AppointmentID )

AppointmentID is an auto-entered serial number field.

Note that both methods will trigger on record committment only.

and this one too:

In general, a join table needs only the two keys corresponding to the parent tables' primary keys. Those would not be compound keys.

You could have additional keys (or compound keys) for other secondary relationships into the table, often for filtered relationships.

In FM 6 and below, a "compound key" would be a calc with several fields concatonated. FM7 and up, compound keys would be done differently; using the new multi-criteria relationship, you'd have each separate field matched in the relationship definition.

A primary key must satisfy two conditions:

1. It must be unique (within its own table);

2. It must be permanent.

A primary key cannot be permanent if it depends on any attribute of the object it identifies. For example, the name of an object may be changed by the user; if the name is being used in the primary key, all children records of the object will become orphans.

It is best to use an auto-generated serial number as the primary key.

that makes sense.

if I understand correctly then, the posts that I quoted above were exploring techniques that could be used to enforce specific business rules designed to prevent the creation of certain types of duplicate records?

thanks so much for the quick response and the excelent advice!

the posts that I quoted above were exploring techniques that could be used to enforce specific business rules designed to prevent the creation of certain types of duplicate records?

Yes.

Yes.

thanks again