I'm learning Filemaker Pro Advanced while designing my first relational database from scratch and have run into a design issue; I can't decide what the best way to resolve the problem is and need advice (not just another video to watch; the videos are swell, but just aren't answering my specific question.)
The database I am building will provide internal employees with a historical record of their highly specialized supply orders and vendors.
The part of the design I'm working on at present involves resolving a many-to-many relationship between an EMPLOYEES table and the ORDER RECORD table. Here's the sitch: There are many Employees. There will be many Order Records. Each Order Record involves three employee roles: an Order Requestor, an OrderReceiver, and an OrderProcurer. One or more Employees may be in one or more of the three roles a single Order Record. Any of the employees should be permitted to play any of the three ordering roles. The order record layout is where I want to assign an employee to each ordering role for that specific order record.
Initially, I thought I should be able to resolve this through a single join table - but that seems to be wrong, as I have three roles for Employees to play.
Then I thought I could resolve it by creating three join tables for each role - but that seems to demand duplicating tables (due to the repeating primary/foreign key) - and still didn't seem to produce the result I wanted.
Then, I am thought I needed to create three tables for each role that all connect to the primary key of the EMPLOYEES table and then connect to the ORDER RECORD table through its own primary key - but it still doesn't seem to work.
Now, I mostly think there must be a far, far simpler solution that I am just not seeing yet. Anybody out there have a clue?
Many thanks for your help!!!