Relationships in a job tracking database

[ALT147]

Hello.

I've kind of been thrown into the deep end in terms of database software. Having no idea even about what a relational database is, I've been asked by a friend to come up with a job/personnel tracking database for their small business. I downloaded the trial version of FileMaker Pro 8, so I now have approximately 26 days to try come up with something that will convince them it is possible.

I began by fiddling with the "Task Management" template that came with FM 8, as it seemed closest to what I wanted to achieve. But now I'm stuck, and I think it's because I don't really understand the idea of relationships.

Basically, what we want is this: someone will create a "job card" for each project undertaken, with information about who, what, when, how much, etc.

In the "Projects" tab, we want to view information about each project, and about who worked on it.

Then, in the "People" tab, we want to be able to view information about each person, and about all the work they've done on any project. This is the part I'm having trouble with (see attached). I can get the "Projects" tab working basically how I want it to, but I can't get a list of *all* the projects person X has worked on in the "People" tab.

There are other little things I'd like to be able to do, including:

i) Have a "Pay Rate" field in the "People" tab defining that person's pay rate for used in the "Pay" calculation displayed in the "Projects" tab

ii) Have the ability to display projects that a given person has worked on only during a specified time period (plus total pay earned during that period, etc.)

But getting all projects to display in the "People" tab would be a good start. I have a feeling that my relationships diagram is totally off the mark...

Any help is greatly appreciated, and I'd be very grateful if things were explained in simple terms: as I said I have no database experience other than that I've garnered in the last few days from reading help files. Thanks.

projects.fp7.zip

[aaa]

Hi, ALT147!

I think that you can easy solve your task using "many to many relation". You must have three tables:

Projects, Persons joined with Project_Persons table.

Having in the third table date when person added to this project you can answer to all your question for any period.

[ALT147]

Hi aaa. Thanks for your reply.

A quick Google tells me that a many-to-many relationship is: "A relationship between two tables in which one record in either table can have many matching records in the other table."

That's exactly what I want! Thank you!

The only trouble is I still can't work out how to achieve this. Would you be able to tell me exactly what to do: ie, exactly which table fields to link to which others? Or point me to a simple example database which has such a relationship in it? Although I can find a definition, Google seems to have very little on how to actually implement a many-to-many relationship.

Thanks again.

[aaa]

I will try to create attachment on your example.

[aaa]

Here is example of many-to-many relation based on "projects" and "persons"

Projects.zip

[ALT147]

Thanks for that file, aaa. I think I'm starting to get the idea. I tried imitating your relationships diagram, and it works now... almost.

The only problem is that if I enter the same person name under two different projects, there will be two people with the same name in the "People" tab. I'm guessing this is because I linked the ID numbers of projects and people to the join table, each of which is unique.

I've attached the file... you'll see that there are two instances of Persons A, B and C in the "People" tab. I want all the projects that Person A is working on to appear on the one page. Is that possible?

I really appreciate your help with this. Thanks again.

projects.zip

[aaa]

Hi, ALT!

Information about working on the projects must be in the table "Both". If you do such, you will not have problem. I if have time will try to do it.

Relations must be from Projects to Both and from Peoples to Both with allowng create record in Both table.

Edited May 18, 2006 by Guest

[aaa]

Again hello, Hi!

I changed your solution for your goal.

But you must work on this project for your reports, i not understand what is equipments, you can create your relation such, that you will can put information to Both Table from Peoples and Projects.

In all case i think that you will understand the idea of tables work.

Task_Management.zip

[ALT147]

Hi aaa. Thanks for taking the time to edit my file. I'm hoping I'm not being too annoying, but it's still not quite what I had intended.

I want it such that when you enter a person's name under a project in the "Projects" tab, a record is created for them in the "People" tab; *if* one doesn't already exist. I also want it to be impossible for there to be two people with the same name (for obvious reasons).

In practice, if you add Person A to Project A, then a record should be created in the "People" tab. Then, if you also add Person A to Project B, this project should appear on the same record in the "People" tab. Is this possible?

Oh, by the way, don't worry about the equipment column. I'll worry about that later on. It's not really very important anyway.

Thanks.

[aaa]

Hi, ALT!

This works such as you want. I little changed file that will be clear for you. Look attachment.

When file opens you will see list of projects.

If you want any project you click at it and you will see the form of this project. You can note that if you PersonA put in two diferent projects, in form for PersonA you will automatic see two project in which PersonA works. "People" and "Projets" i changed such that they opens the lists.

Task_Management.zip

[ALT147]

Hi aaa.

Adding a new person to a project doesn't create a record for that person, and I can still make two people with the same name.

Is it not possible to change these things?