How to make this database relation system

[entheologist]

I have a database table of plants, one of chemicals, one of products, one of illnesses. Each table is connected, for example lets say opium poppies. They contain the chemical morphine. Morphine is used in painkiller products. Its used to treat pain and other illnesses. On the opium poppy page, I want to list all the chemicals found in the poppy. All the products (i.e. opium, laudanum) made from the poppy. All the illnesses which are treated with the poppy. Users need to be able to edit this page, and enter chemicals, products or illnesses that are related to this plant. They also need to be able to edit the info on the page.

 

Making database relations is a pain in the ass with CakePHP. I don't want to waste any more time so I'm thinking maybe making it as a wordpress plugin is the way to go.  How would you go about this? Is there an easier way than wordpress, like a better CMS, or framework? If I use wordpress, should I do all the DB interactions with AJAX to avoid having to interact with the DB through wordpress (this gave me massive trouble in the past since I had my data in an external db)?

[ginerjm]

Methinks you are in the wrong forum

[Barand]

The way you have described it suggests a simple hierarchy
 

+------------+
|  plant     |
+------------+
      |
      +-----+ (contains)
            |
        +------------+
        |  chemical  |
        +------------+
              |
              +-----+ (used in)
                    |
                +------------+
                |  product   |
                +------------+
                      |
                      +-----+ (treatment for)
                            |
                        +------------+
                        |  illness   |
                        +------------+

However, before we can give definite advice on how the db relationships should be set up there are many questions to be asked about the actual relationships between these four entities. For example

 

• Can an illness to be treated by more than product?
• Can a product treat more than one illness?
• Can a product contain more than one chemical?
• Are any chemicals used in more than one product?
• Do any chemicals come from more than one plant?
• Do any plants produce more than one chemical?
• etc

The answers to questions like these may well lead to a far more complex network of relationships

[entheologist]

The issue there is on say the pain page, I'll need to list Compounds: morphine, Plant remedies: opium poppy, Medications: Morphine XR

 

Every table has multiple relations to each other table.

[entheologist]

Barand: Yeah each plant contains multiple compounds. A compound can be found in multiple plants. A product can contain multiple plants or compounds. An illness can be treated with multiple substances. I setup a relational table that takes care of this, the table has a column for plant_id, compound_id, product_id, illness_id. CakePHP isn't really compatible with composite keys like that though. I had this relational system up and running in a wordpress plugin, but there were so many glitches I abandoned that and switched to CakePHP.

[entheologist]

I decided to use wordpress to make the CRUD system to simplify things. I know how to do this relation thing, but I don't know if I'm doing it right. I made a single relational database which contains columns plant_id, compound_id, product_id etc. Would I be better off making a separate table for each relation, i.e, a plant_compound table, a plant_product table, a product_compound table. I think I just answered my own question there, that would be way less practical than just using one table.

[Barand]

Would I be better off making a separate table for each relation, i.e, a plant_compound table, a plant_product table, a product_compound table.

Yes.

 

Where you have a many-to-many relationship (eg one plant has many compounds and one compound can come from many plants) then you need an intermediate table to associate the records

+------------+                                     +------------------+
| plant      |                                     |   compound       |
+------------+                                     +------------------+
| plant_id   |--+                              +---| compound_id      |
| plant_name |  |                              |   | compound_name    |
+------------+  |                              |   +------------------+
                |                              |
                |                              |
                |     +---------------------+  |
                |     | plant_compound      |  |
                |     +---------------------+  |
                |     | id                  |  |
                +---< | plant_id            |  |
                      | compound_id         |>-+
                      +---------------------+