Finding records in a many-to-many relationship
Hi all, hope you can help.
I am developing a database to keep track of our clients' computers, their serial numbers etc. I have tried to make the database structure as extensible as possible so forgive me if it sounds at all complex in terms of relationships:
I have also set up link tables between the 'core' tables, as I wish to have many-to-many relationships as follows:
In 'plain English':
- People can be part of one or more organisations
- Computers can belong to one or more people
- Computers can also belong to one or more organisations
At the moment I can browse and search by person or organisation. I can then view the devices directly associated with them, via the link tables and my relationship diagram.
Now, let's say an organisation has a Dell PC. The ID of the PC is linked to the ID of the organisation that owns it, using the link table. So, it's searchable.
One of its employees brings a MacBook to work, and we want to record that too. So, we link the MacBook to the person, rather than the organisation, because it belongs to that person explicitly. Semantically this makes sense.
Suppose the person with the MacBook phones up with a problem and I want to look up the computer details. Although I can go to that person's record to locate the MacBook, equally I would like to be able to look up their organisation as well and access the Macbook's details that way. Relationships exist to link the MacBook to the organisation via the person, but I can't work out how to display both the direct and indirectly linked computers in a single layout.
- I wondered perhaps if I can use a portal, but ideally I'd like to use a filtered list layout since it works much nicer with FM Go :-)
- I think it perhaps needs an "OR" find request, saying "show me all the computers from these two link tables (computer-org & computer-person) where the ID of the organisation is X OR the ID of the person is Y"
All help greatly appreciated. Thanks!