Jump to content

How to make this database relation system


entheologist

Recommended Posts

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)?

Link to post
Share on other sites

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

Link to post
Share on other sites

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.

Link to post
Share on other sites
  • 2 weeks later...

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.

Link to post
Share on other sites

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         |>-+
                      +---------------------+
Link to post
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.