Messed-up Relational Design

[BikeBoy]

Hi there,

Ive inherited a nightmarish solution, seems like a classic One-to-Many, but the IDs are problematic:

File1 is named *Work*, File2 is *Salary*.

File1 has fields:

Employee ID (4 digit number)

Machine ID (1 digit number)

Jobs (numerical code)

Hours (number)

PK_ID (combination of Employee ID- Machine ID), not necessarily unique. This is the Many side of relationship.

File2 has fields:

Employee ID (4 digit number)

Machine ID (1 digit number)

Salary per hour (number)

PK_ID (combination of Employee ID- Machine ID), here it’s unique. This is the One side of relationship.

These two files are used to calculate the earnings of each employee by multiplying Hours * Salary per hour. Employee only works on one machine, but can do different jobs.

Right now two files are connected on PK_ID because its meaningful to both. Theres no problem calculating total pay per employee, but there is a problem separating the pay per job, since Job is not a field in File2.

I want to normalize, and make separate File3 called Job. I want to create one relationship between all 3 files based on PK_ID, and specify *Allow creation of related records*.

Then second relationship will be based on combination of Job-Employee ID- Machine ID between all 3 files. That way each record will truly be unique in each file.

Is that a good way to do that? I know recommended PK is just a sequential number, but in this case I need connection on something meaningful to all files.

Thanks for your suggestions

[Ugo DI LUCA]

Before you start with this new setting, one main question is to be answered. Is it 100% certain that the employee would always work on one unique machine ?

If it's likely to change (or probable), set a many to many relationship based on either the employee, the machine or the Job Id and you'll be able to live with this file for many years...

[Kurt Knippel]

Honestly, I cannot see how the machine is even necessary.

One thing to do is to start by dropping the whole File1, File2 reference. Call them by name.

Secondly, make sure that everything in each of those databases, describes one thing and one thing only.

Before starting this (and you are on the right track here) pickup a good book on relational design. Try Chris Moyer's and Bob Bower's latest book Filemaker Pro 5.5 Techniques for Developers. They have a whole section on relational theory and how it applies to Filemaker.