Newbie needs help with relationship logic
I'm somewhat of a FileMaker Pro newbie. I've been using it for a while but not to the level I would like. I've created a database but am starting over and need help with a relationship logic question.
I understand one-to-many and many-to-many relationships, and why all relationships need to be resolved to be one-to-many.
Here's what I'm trying to do...
I have about 700 users with various pieces of data (email address, password, etc.) Their email address is a unique field to this table and would likely be the table's primary key.
I also have about 800 electronic devices that are assigned to these users (most assigned, some just in inventory). This table includes the serial number and other information about each device. The serial number would be the primary key.
My device table actually also includes user assignments, but this information changes frequently so I need to import new data every so often.
My problem is how to set up the relationship between these tables. Right now this is a many-to-many relationship because users can have had multiple devices assigned to them over a period of time; devices can be assigned to multiple users over a period of time as well. What's most important is that I can keep track of current assignments and history of past assignments/activity.
Can anyone give me some advice on how to set this up?
Thank you in advance!