How to cross reference tables

[CrimpJiggler]

I have a table containing chemicals (i.e. drugs), a table of plants, a table of preparations and a table of products. These tables need to be connected though. As an example, say I add opium to the preparations table. Opium is prepared from the opium poppy (papaver somniferum) so I'll need to fetch papaver somniferum from the plants table. Opium contains morphine and codeine so I'll need to fetch those from the chemicals database. Morphine is the active drug in various pharmaceutical products such as Oramorph. I also have an ailments table, so in this case morphine is used to treat pain and some other ailments. So these tables need to be linked somehow. For the chemical database, I could add a field for plants, preparations and products that happen to contain morphine but then I'd have to add a chemicals, products and preparations field to the plants table etc.

 

What is the correct way of handling this kind of thing?  I added a new table that links the items to each other:

 

so in that table you can see com72 is the identifier for item 72 (meaning the row with ID 72) in the compounds table so as you can see ID 72 (lets say its morphine) is present in plants 2 and 3 (lets say opium poppy and another plant). Then for plants 2 and 3 I also have to add entries containing all the ailments, compounds, preparations and products they are linked to. Is this the right way to handle relationships between tables or is there a better way?

[Barand]

No, it is not. You need to normalize your data.

 

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

 

for example, a drug can treat many ailments, an ailment can be treated by many drugs. To resolve many-to-many relationships like this you create an intermediate table containing the drug id and ailment id