Jump to content

Concatenated Compound Primary Keys vs. multi-criteria relationships


Woodnote
 Share

This topic is 4416 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Untitled.jpg

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.

  • Like 1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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!

  • Like 1
Link to comment
Share on other sites

This topic is 4416 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.