Jump to content


Photo

How to make this database relation system


  • Please log in to reply
6 replies to this topic

#1 entheologist

entheologist

    Member

  • Members
  • PipPip
  • 10 posts

Posted 28 July 2014 - 12:48 PM

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



#2 ginerjm

ginerjm

    Advanced Member

  • Members
  • PipPipPip
  • 1,713 posts
  • LocationVoorheesville NY

Posted 28 July 2014 - 01:29 PM

Methinks you are in the wrong forum


JG

PS - If you're posting here you should be using:
        error_reporting(E_ALL | E_NOTICE);
        ini_set('display_errors', '1');

at the top of ALL php code while you develop it!


#3 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,374 posts
  • LocationCheshire, UK

Posted 28 July 2014 - 02:13 PM

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


moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#4 entheologist

entheologist

    Member

  • Members
  • PipPip
  • 10 posts

Posted 28 July 2014 - 05:08 PM

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.



#5 entheologist

entheologist

    Member

  • Members
  • PipPip
  • 10 posts

Posted 28 July 2014 - 05:51 PM

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.



#6 entheologist

entheologist

    Member

  • Members
  • PipPip
  • 10 posts

Posted 06 August 2014 - 07:55 PM

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.



#7 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,374 posts
  • LocationCheshire, UK

Posted 07 August 2014 - 02:31 AM

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         |>-+
                      +---------------------+

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com