I am building a basic CRM for my events company. I have the following tables
Contacts - contains basic personal info fields and primary, secondary, tertiary jobs for various freelancers
Activities (groups of events that belong to the same budget code and have the same vendors and staff)
Events (specific events within activities - not relevant here)
For each activity, there are a list of jobs, i.e. Hair, Makeup, Driver, Photographer etc. These are to be filled by someone on the contacts list.
If I create a field in my Activities table for each job title, linked to records in my contacts table, as I populate the jobs in Activities multiple new fields are created. This seems rather cumbersome, but does allow my core functionality in the Activities table to work i.e. I can see which jobs are filled by whom for which events, and, perhaps more critically, which are not.
I’m believe that the answer may lie in junction tables, and I have created a couple (although I feel a little unsure of this knowledge as yet). What I can’t seem to do is have the “blank slate” with jobs to fill, to ensure all roles are filled on all activities.
Any ideas gratefully received,