Structuring multiple tables with similar characteristics

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
  • (Edited)
Hi all, 

I have a question about structuring multiple tables with similar characteristics. 

What I mean by this is I used to have several tables which I consolidated into three because I realised I could structure them consistently - "categories"; "groupings"; "tags". The nature of the data in the old tables was different, the categories covered countries, services, methodologies, therapy areas etc. Our DB centers around one table in particular - "projects". The idea was all of the aforementioned categories has it's own tags which can be added to a project to try and find it again in the future. 

I have just overhauled the "therapy areas" part (I work in healthcare so these are essentially standardised lists of diseases) and in doing so they really don't fit within the three-tier hierarchy. I also need to do the same with another of the categories so my consolidation is getting more fragmented. My (convoluted) question is - should I keep the other "tags" as they are, or separate all out into respective tables? Note, they would all be two two tables, one as a grouping and the other as the detail.

I hope this makes sense. Looking forward to hearing thoughts.

Matt
Photo of Matt

Matt

  • 62 Points

Posted 10 months ago

  • 0
  • 1
Photo of Debbie Taylor

Debbie Taylor

  • 604 Points 500 badge 2x thumb
Matt - There isn't a "right" answer.  I'd recommend consolidating your tags into two tables: groupings and details. Create a one-to-many relationship between groupings and details,   In the grouping table, add a field that reflects the type of tag (eg therapy area, job title, symptom.)  That way, it will be easier to manage the data.  

Conditional drop-down lists will become your new best friend.  That functionality will let you limit the choices users have when tagging things.  Feel free to reach out to me at CloudBase Services for more help.
Photo of Matt

Matt

  • 62 Points
Thanks Debbie, this helps. For the most part it is set up as you described, using conditional drop downs. My dilemma came from some of the tags needing to be disaggregated because they don't fit in the three tier structure but it seems it's probably best (and a lot simpler) to keep the other tags as they are.

Cheers