2 Replies Latest reply on Dec 27, 2013 6:04 AM by haarbol

    When to use a category field, when to use a seperate table

    haarbol

      Hi,

       

      Imagine i have a few types of biological species, for instance insects, mammals, birds and plants - and i want to categorize all subtypes in a database,

      to see where they occur. Imagine they share most properties (English names, Latin names, habitats), though not all.

       

      Do you create new tables for every category? Or two, one for the animals, one for the plants -

      or do you keep them all in the same table and just use a category to select what kind of entity it is?

       

      It feels to me like i can put them in one table and if i want to look only at the plants, it would be easy to display a filtered version of that table
      with only the entries that are tagged as 'plant'. But i got this nagging feeling that that might not be the best solution.

      What are the drawbacks and advantages of both methods?

        • 1. Re: When to use a category field, when to use a seperate table
          Oliver_Reid

          One model for this siutaion is to use one table for the common elements (incuding acategory field, and sepete related tables for "properties_plant", "properties_mammal", etc. for the fields specific to each species subtype. This is a little more efficient than using a single table. Y

           

          You could regard this set up as single parent table with altertiave child tables, or each subtype table as a parent table, all sharing a common child table conataining unoformly strctired data.

           

          The drawbacks of using completely seprate tables is that when you decide there is new common field, there are many table to change, and that is it harder to search across mutiple subtypes

          1 of 1 people found this helpful
          • 2. Re: When to use a category field, when to use a seperate table
            haarbol

            Alright. Thank you. The possibility of using parent and child tables hadn't crossed my mind yet. Interesting.