TABLE RELATIONSHIPS

[KIERAN]

I have a database recording bookings for flights from a
website.To establish a primary key in each table, i've
had to use multiple fields. e.g. In the table PNR
(passenger name record), the primary key
is "rec_loc","type" & "modified_date". In the table
PNR_PAX the primary key is "rec_loc", "modified_date"
& "ticket_number"

I need some advise on the best way to link these tables
and what type of relationships to create.

Currently if I run any queries I seem to be getting a lot
of duplicate records.

 

[Tim Ferguson]

I need some advise on the best way to link these tables
and what type of relationships to create.

You make relationships based on compound pks in exactly the same way as
single-field ones. You set up the fk fields to match _exactly_ (and this
includes the order of the fields) and then create the relationship. In the
relationships window, just ctrl-click the fields in the table model and
drag to the other one.

Currently if I run any queries I seem to be getting a lot
of duplicate records.

You may have problem with the SQL joins, or you may have a problem with the
schema design. Difficult to tell from here.

One caveat: if you use datetime values in primary keys, you have to be very
sure that you are trimming off any time values. A value of #2000-04-19# is
not the same as #2000-04-19 12:32:00#, and even two times that look the
same may actually be different at the binary level. For that reason, I
often use a Long Integer to hold dates that are going to he used in keys.

Best wishes


Tim F