Join Tables in Anchor-Buoy

[Amanco]

Hello, 

I need help please as I am new to the Anchor-Buoy using the following concept:

I have a base table [PATIENTS] and would like to connect it to [STATUS], [DOCTORS], [TESTS], [TREATMENTS]...etc

I looking to track the BOUYS Tables changes with time e.g. 

(A) Track the start/end dates/times of any [STATUS] changes of the patient --> becoming INPATIENT or OUTPATIENT or DISCHARGED

(B) Track the start/end dates/times of [DOCTORS] interactions with [PATIENTS]

...etc

1) I figured a join-table is the approach to capture this data, is this correct? or is there a better way?

2) Using the anchor-Buoy method, how do I use a join table? is it ONE JOIN TABLE for all [ANCHOR-JOIN-Buoys]  or each Buoy gets its own JOIN table? I have also read about CONNECTOR-SELECTOR method.

Please advise me about the best approach.

Thank you

Edited December 27, 2020 by Amanco

[Wim Decorte]

For (A) - What is the purpose of the STATUS table?  Is that to record when the status changes, for a particular patient?  In that case you don't need a join table between STATUS and PATIENTS.

For (B): look for the nouns in that sentence.  Feels like you need a table called INTERACTIONS.  Or perhaps you have another table already tagged for that but call it differently?

You may be overthinking the "join table" bit.   You'd typically only need a join table to accommodate a many-to-many relationship.  For instance if one patient can have multiple tests but one test covers multiple patients (unlikely - but you get the drift).

The only place in your narrative where I see a join table potentially are the interactions, if one interaction is with multiple patients at the same time.

Barring that; all you have are normal relationships with no need for join tables.  (it doesn't help of course that in relational database speak and especially in the SQL language, a JOIN is just another word for a relationship).  In FM speak it carries that special meaning of many-to-many, where in SQL it does not.

In general, when you need a join table it is to cover one specific type of relationship.  Like "patients involved in a visit".  So don't think about it too generically like "each Buoy gets its own join table", think of it more simply in terms of what you want to cover.

 

[comment]

Anchor/Buoy is a method for organizing the relationships graph. Whether you use it is largely a matter of convenience and personal preference. It has nothing to do with the question of what tables you need in order to track your data.

If you want to keep a history of patient's status, then you most certainly need a join table between PATIENTS and STATUS. However, if the STATUS table contains only values like INPATIENT or OUTPATIENT or DISCHARGED, with no information describing these values, you could dispense with it and use a custom value list instead. In such case, the table keeping the history would technically no longer be a join table - but it would still be one conceptually. 

If all your join tables are similar - i.e. tracking the start and end of a join (and nothing else) - you could consider rolling them into a single "wide" table, with a separate foreign key field for each table that can be joined. A side advantage of such arrangement is that it would allow you to organize the graph with the join table serving as a hub and the joined tables as spokes - thus reducing the number of table occurrences. But I doubt that all your join tables would be similar - for example, the join table to TESTS is not likely to have the same structure.