Join tables, I think...

[Ben ECA]

Hello all

I'm building on the projects starter solution for my company. It is to manage client records and projects, obviously.

We do university/school appliations for them, and also visa applications and various other bits of work.

We call staff members caseworkers, and I know I should probably choose one or the other in terms of organising the database, but I didn't.

Each client is assigned to a caseworker who is then in overall responsibility of that client, but when we start a project for a client, the project may be assigned to a different caseworker (eg, one caseworker does the client's university application, while someone else does their visa application)

• A member of staff has many clients
• A client must have one, only one, staff (caseworker). a client's caseworker may change later
• A client always has one, and maybe more, projects. (if no active work, there will be a project set to follow up with a sales call when their visa is expiring for example)
• Staff have many projects.
• A project caseworker may or may not be the related client's overall caseworker
• A project must always have a client and a project caseworker

What I can't work out is how to do the join tables to link a client to a staff (caseworker), and clients into the projects.

Then I will need to have a way of selecting a staff member when a new lient is created

Also selecting a client and a staff member when a new project is created.

If anyone can help get me started, I'd really appreciate it. I think once I can get this done, the other work left to do will start to make more sense.

Thank you

Ben

Projects2.fmp12

[comment]

There are (at least) two things I did not get from your description:

2 hours ago, Ben ECA said:

A client must have one, only one, staff (caseworker). a client's caseworker may change later

Do you need to keep a history of these assignments? 

2 hours ago, Ben ECA said:

• A project must always have a client and a project caseworker

Can a project have more than one caseworker?

[Ben ECA]

Thanks comment

A history of the assignments would be a good addition, actually.

No, a project can only have one caseworker. If any other staff were involved, it would be as a resource in tasks under that project. The idea of caseworker is that they teke responsibility for the project, so only one caseworker per project. The caseworker could change during a project, but not usually.

 

[comment]

2 minutes ago, Ben ECA said:

A history of the assignments would be a good addition, actually.

Well, think about whether you need it - because it will add complexity to your solution. Without it, all your relationships are one-to-many, so I don't see why you would need any join tables. Here's an ERD that I believe summarizes everything you said in your original description:

 

[Ben ECA]

no, I don't think we need it.

so how would I go about actually creating the relationship?

And I suppose the question is what do I do in the layouts to make it easy to choose a caseworker from the client.project records?

Edited July 29, 2017 by Ben ECA
afterhtought

[comment]

4 hours ago, Ben ECA said:

how would I go about actually creating the relationship?

That depends on your intended workflow as well as on your personal preferences for organizing the relationship graph. Here's one way it could look:

 

where the Staff 2 TO is meant to be used for viewing the details of the selected project manager, and Projects 2 can show all of staff member's projects in a portal placed on a layout of Staff.

 

4 hours ago, Ben ECA said:

what do I do in the layouts to make it easy to choose a caseworker

For starters, I would suggest you use a value list using values from Staff::StaffID (also showing the name). Later on, you can move to a more slick user interface and select from a portal or from a list view.

 

 

 

Edited July 29, 2017 by comment