Q: My department uses a FileMaker Pro database to track the jobs we work on. It's currently set up so we can select the name of an artist who is working on a project from a drop-down list. Some of our projects have multiple artists working on them, though. What would be involved in changing the database so we can specify multiple artists for a job?
A: Making changes to the data model of an existing system can be a bit tricky, which is why, of course, it's important to think it through really well before you build your system. But business rules change — we'll assume that's what's driving your need — and so must data models from time to time. Thankfully, FileMaker Pro is a rather nimble and forgiving tool when it comes to things like this.
There are two things involved in a change like the one you're asking about. The first is making the structural changes needed to support the new functionality. As we'll explore, this involves making changes to tables, fields, relationships, layouts, and scripts. Frankly, this is the easy part. The other task — moving the existing data into the new structure — can be painful depending on the condition of your data and the complexity of the change.
First, we'll discuss the structural changes. For simplicity, let's assume you currently have just one table, Job, which has the following fields: JobID, JobName, and ArtistName. I'll also assume you have a static value list attached to ArtistName. Now, it's possible that you could just add a single new table — named something like Assignment — and end up with the requisite one-to-many relationship from Job to Assignment. One job could then have multiple artists assigned to it. But, properly speaking, an artist can be assigned to multiple jobs, and you'll be better served, in our opinion, by re-modeling this as a many-to-many relationship. The difference is that you'll end up with an Artist table as well. The new data model will look like figure 1.
Create the following fields in the Artist table:
- ArtistID (auto-enter serial number)
- ArtistName.
In the Assignment table, create:
- AssignmentID (auto-enter serial number)
- JobID_fk
- ArtistID_fk
Figure 1: A many-to-many relationship — Because it's possible to assign more than one job to an artist, and more than one artist to a job, you're best served by setting up a many-to-many relationship.