Hello Technet community,
My name Mark and I am a wedding photographer, last year I put together a basic DB on filemaker which has 2 tables (customers and invoices) I learned about relationships and I was able to make it work (basic setup). Now I got really into filemaker and want it to make couple of changes in my data base but I feel there are somethings beyond my knowledge (many to many tables). I will try to describe as detail as possible what I am trying to do, and any help will be greatly appreciated.
1.My previous Customers table had 3 contacts in each record (bride, groom and any mom or dad), which I recently figured that was a bad choice. Also the project info was in the same table so 3 contacts plus project info in each customer table. Then I realize that each customer could only have one project and if they wanted any other session of photography(which it happen) I could only creatte a different invoice but not a project. Also the 3 contacts were seen as one contact and could not have independent projects.
a.- I created a projects table now I can create multiple projects on each customer (basic one to many relationship)
b.- I make each customer only show only one contact and re import the other 2 contacts as new customers with their new IDs.
Now each customer has only one contact and the project is in a different table so each customers could have multiple projects.
*Now my question is this
When I create a new project I want to relate to the customers table and bring them to the project layout( so I have a project assigned A BRIDE, A GROOM, AND ANY PARENTS). Also the MOST important when I bring them to the project I want to assign a ROLE example of roles; bride, groom, sister, mom, dad, etc.This is where I get lost on the roles of the customers, I want a different role for customers on each project so a Maria McBrite could be a bride on one project and a mom on other project or a sister on another project. I figure I will need a 3rd table IF YOU GUYS COULD HELP Me on how I relate this table "ROLES" to CUSTOMERS AND PROJECTS TABLE, or if you may have any other suggestion on the DB Iam trying to put together.
Once again thank you so much for your help and time.
Previous tables : Customers, Invoices.
Future tables: Customers, Projects, Invoices and Roles(which I need help to relate to other tables).