Expand all | Collapse all

Structuring multiple tables with similar characteristics

  • 1.  Structuring multiple tables with similar characteristics

    Posted 01-25-2018 21:19
    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.


  • 2.  RE: Structuring multiple tables with similar characteristics

    Posted 01-25-2018 21:41
    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.

  • 3.  RE: Structuring multiple tables with similar characteristics

    Posted 01-26-2018 09:47
    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.