How to manage databases with groups of somewhat related items

[CrimpJiggler]

I have a database with compounds, plants, preparations and products. They're all connected because they're all substances and sometimes a plant with contain some of the compounds, or a preparation will contain some of the plants etc. I'm trying to figure out if its better to lump them into one table, give them their own tables or what. What I did was something in between:

I created an items table which contains only the fields that all these things contain, then I made extra tables for compounds, plants, products etc. that contains all the additional data specific to those groups (i.e. melting points for compounds, natural habitat for plants, manufacturer for products etc. and then use that code (com, pla, pro) to join those additional tables. See the way there are 2 ID numbers before the item name, the first is the item ID, and the second is the ID for the additional table so for example rosemarinic acid is item 31 but compound 96.

 

I'm switching over to cakephp right now so if this is a bad approach to DB management I might as well change things now. I also have a relational table which connects these 4 groups together, so for example opium there contains morphine, codeine and papaverine, and its found in the papaver somniferum plant so I need to make all those links. Should I stick to this way of doing things?

Edited January 4, 2014 by CrimpJiggler

[sKunKbad]

I think I would have separate tables for plants, compounds, etc, and then have a cross referencing table. So for instance if you have plant #1, and it is related to compound #12, you would have a reference in the cross reference table:

a_type = plant
a_id   = 1
b_type = compound
b_id   = 12

You would end up with multiple rows if plant #1 had a few compounds, but this would make it easy to do cross references because if you query the cross reference table you can see that plant 1 is related to compound 12 ( or compound 12 is related to plant 1 ).

 

Maybe somebody else has a better idea, but your original proposed solution seems like it is messy to me.

[Barand]

The answer is one word - normalize.

 

http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360