New database project, struggling to find a table/relationship structure that fits
I'm relatively new to FMP but have worked with databases & tables before - unfortunately not experienced enough to know the answer to my question, so I need everyone's help! Somebody handed me this project after they had a crack at it, they had no experience with databases at all and had watched a few videos and suddenly reached their limitations. So the tables & layout are already in place, but the database isn't live so I can carve it up however I need to. If my description of the fields sounds a bit weird, it's because I've had to change a few details for confidentiality reasons.
The simplest way to describe the database is to say that it starts out as a simple contact database - a Person table with fields like name, address, nationality, photo, next-of-kin, etc etc. Additional information stored per person is passport, visas held, current annual leave details, and radios 1 & 2 (some of the people on this database carry 2-way radios). Because this database is for managing a team of staff & security which travel around a lot, its main function will be to manage the travel paperwork, visas, declarations, etc for the group of people, associated staff and security people. Currently all the travel paperwork for a team of 30 people has to be filled out every few days, and the idea of using an FMP database is to have this paperwork produced with a few mouse clicks instead, as well as making it easier to track, report on, etc.
So the database design currently consists of a Person table with those records, as well as a primary key field PersonID. There is also a Radios table containing fields serial_num, make_model, battery_type, and PersonIDfk as the foreign key.
Anyway so my basic problem is this: in the case of security staff, they may carry 1-2 radios as part of their duties. These radios get reassigned from time to time, but a person will typically keep their radios for the duration of the overseas trip. So you can say that each record in the Person table may have 0, 1 or 2 radios.
The original author of the DB had created a Radios table and used a 1:many relationship, and in the Person layout they had tried to tie the two radio "slots" (by which I mean, sets of fields (serial number, make_model, etc) for radio 1 and radio 2) into that Radios table. However this doesn't work for a multitude of reasons, as I found out - I even tried creating a second table-occurrence of Radios in the relationships graph, but that didn't behave as required, you still end up with two slots displaying the same radio - presumably because, in the case of a 1:1 relationship (with two table-occurrences of Radios) FileMaker looks through the Radios1 occurrence for a PersonID=PersonIDfk match and pulls out, say, radio #123 and so your first radio slot on the Person layout now shows the info for radio 123; it then looks in the Radios2 occurrence for PersonID=PersonIDfk match but of course it finds the first match (123) instead of finding the second radio assigned to that person, say radio #456. So both radio slots on your Person form now show the info for radio 123.
It seems that it's quite easy to make a DB that could handle one radio per person, just set up a 1:1 relationship between your radios table and your Person table. Likewise with your typical Customers and Orders DB, you have a 1:many relationship because a customer can have between 1 and infinity orders.
But I am really struggling to find a way to implement a 1:2 relationship, e.g. a 1:many relationship where the Person cannot have more than two Radio records associated with them (and there has to be uniqueness, e.g. you can't assign radio#123 twice to Person A, or radio #123 to both Person A and Person B). I did try an implementation where the radio fields are simply stored within the Person table (i.e. a single table approach), but after consulting with the end customer it turns out that radios do get reassigned between security staff fairly often, so it does make more sense to hold this information in a separate table, so that reassignment is merely a case of altering links between a Person record and the associated Radio records.
I hope I've explained myself well, and I'm hoping that there's an easy answer to this. As you may have gathered, I understand tables and fields and records reasonably well but don't have a lot of experience in DB design, and my project is not something which easily fits any of the included templates or training examples out there.
It could even be that I need to start from scratch with this, and find a better way to work with this information. For example, what if the radios assignments were done on a separate layout which worked directly with the Radios table, and this information was only *displayed* (read only) via a "Look up related records" area on the Person form? Or would I still come up against the same issue in this separate radios layout..?
Any suggestions welcome!