Cross referencing

[horsebox]

I have 3 database tables a plants table, a chemicals table and a preparations table. Some plants have chemicals in them and there are preparations that can be made from them. I need a way to cross reference them to keep track of which chemicals are present in which plants and preparations and which preparations contain which chemicals and plants etc.

 

What I did right now is add a preparations field and a chemicals field to the plants table. A plants field and a preps field to the chemicals table etc. In these fields I list the ID number of the items to be cross referenced. Is there a simpler and more effective way to do this? I'd really rather not have these extra fields in each table I've heard of people creating separate tables for cross referencing. is that the most effective way to do it?

[jskywalker]

creating a table with (at least) 3 fields:

1) plant-id

2) chemical-id

3) preparation-id

 

Every record in this table will show a plant linked to a chemical and to a preparation, if any of these fields is not applicable, you can set them to NULL.

This way you could have more plants/chemicals/preparation per plant, and i dont see how you would do that now...