I’m trying to decide on the best way to structure my database, in terms of connecting different types together. I know there are a few different ways to do it, and I read the data structure page in the Bubble manual so I’m familiar with the 3 options under the “Example scenarios” section. But still not quite sure what to pick for my use case.
The project I’m working on connects drugs, studies and claims all together.
Here are the basic use cases:
A user can pick a drug and see all the studies and claims for that drug.
A user can pick a claim, and see all the drugs with that claim, and also the studies for that drug supporting the claim.
A user can pick a study, and see what drug was in it, along with a list of claims for that drug (specific to that study)
Right now there’s a lot of redundancy - I have them all connected like so:
- Drugs connect to a list of claims and a list of studies
- Claims connect to a list of drugs and a list of studies
- Studies connect to a list of drugs ( I haven’t connected them to claims, not sure if I need to, since I can lookup the claims of the connected drugs)
I also went ahead and created what the manual describes as a “joining table”.
It’s a data type called “DrugClaimStudyDose” with the field types of Drug, Claim, and Study, and the text field of “Dose”.
“Dose” comes from the study table, and is needed because some Drugs have different claims at different dosages.
So in this “DrugClaimStudyDose” data type, I create a new thing for each drug + claim + study + dose.
Now…the manual says this “joining table” above is more scalable. I haven’t quite wrapped my head around how I’d use it, I guess I would just make EVERY page that data type, instead of a “Drugs” page and a “Claims” page, etc?
Any suggestions on the best way to approach this? I’m not sure all the redundancy is needed, and like the manual warns, there’s a chance of data consistency problems since it’s easy to forget to assign all the types together, every time.
Thanks in advance.