I wish to set up a "client" relational database with fields firstname, lastname, address, one or more next-of-kin names and addresses, one or more (can be as many as 20) medical conditions.
The next-of-kin (NOK) details should be in a relational database.
The medical conditions (MC) should also be in a relational database as these are pre-defined, one line, medical condition names that could occur in one or more clients, so as far as I can see a many-to-many relationship.
I wish to be able to list those clients with a specific condition but also make a medical data-sheet that lists the client's details with medical conditions.
I have set up quite a number of relational databases but in this case I struggle with the notion that both the NOK can be one or more links to the NOK data, i.e. the client has more than one NOK and the MC can vary between one and say 20 conditions for each client.
1. what structure should I use and what specific link fields do I need for the relations?
2. how can I use portals to enter the NOK & MC's and change/delete NOK & MC when accessing the clients data, i.e. ideally the NOK records and MC records should be entered "on the fly" when creating a client record.