2

I working on the following ERD, trying to understand what is the best way to connect the entities in the example of plants! For now I'm just focusing on the right side of the ERD (yellow, orange, green)!

Logic in English:

  • Each plant has planting / growing / harvesting requirements!
  • The planting / growing / harvesting requirements may have different values and descriptions!
  • All plants have only one table to hold planting requirements.
  • All plants have only one table to hold growing requirements.
  • However some plants may have one or more harvesting requirements.

My question: What is the best way to show the harvesting requirements?

An example:

  • Cucumber:
    • planting requirements values
    • growing requirements values
    • one harvesting season:
      • values
  • Peas
    • planting requirements values
    • growing requirements values
    • two harvesting season:
      • values
      • values

enter image description here


UPDATE:

I'm having some issues implementing the above ERD to MySQL!

Here is how I create the two tables:

CREATE TABLE plant(
    plant_id INT AUTO_INCREMENT,
    taxon_name VARCHAR(25),
    common_name VARCHAR(25),
    general_description TEXT,
    image BINARY,
    cultivation_group_id INT,
    nutritional_info_id INT,
    disease_id INT,
    planting_req_id INT,
    growing_req_id INT,
    harvesting_req_id INT,
    PRIMARY KEY (plant_id)
);

CREATE TABLE harvesting_req(
    harvesting_req_id INT AUTO_INCREMENT,
    number_of_harvests INT,
    descriptive_growing_season VARCHAR(25),
    min_length_of_growing_harvest FLOAT,
    max_length_of_growing_harvest FLOAT,
    harvest_start_date DATE,
    PRIMARY KEY(harvesting_req_id)
);

ALTER TABLE plant 
ADD FOREIGN KEY(harvesting_req_id) 
REFERENCES harvesting_req(harvesting_req_id) ON DELETE SET NULL; 

Here is the table for harvesting_req using SELECT * FROM harvesting_req;:

harvesting_req_id number_of_harvests descriptive_growing_season min_length_of_growing_harvest max_length_of_growing_harvest harvest_start_date
1 2 Annual 55.0 75.0 2017-01-15
2 2 Annual 55.0 75.0 2017-06-30

But when I try the following query I get only one start date:

SELECT common_name, harvest_start_date
FROM plant, harvesting_req
WHERE plant.harvesting_req_id = harvesting_req.harvesting_req_id;
common_name harvest_start_date
Peas 2017-01-15
1

I would tend towards a one-to-many relationship between Plants and Harvests.

i.e. the Harvests table has a foreign key to Plants allowing as many harvests as desired. The Harvests table would have all the details relating to a single harvest, such as Start Date, Minimum Duration of Harvest, etc.

I'd combine the two green tables. If the start date for one harvest is earlier than the start date for the 2nd harvest, then that's just showing them correctly ordered in the presentation layer (i.e. by an ORDER BY clause on StartDate).

I've created a simple fiddle to show you how I'd implement the design here.

This is the DDL:

CREATE TABLE plant(
    plant_id INT AUTO_INCREMENT,
    taxon_name VARCHAR(25),
    common_name VARCHAR(25),
    PRIMARY KEY (plant_id)
);

CREATE TABLE harvesting_req(
    harvesting_req_id INT AUTO_INCREMENT,
    plant_id INT,
    number_of_harvests INT,
    harvest_start_date DATE,
    PRIMARY KEY(harvesting_req_id)
);

ALTER TABLE harvesting_req 
ADD FOREIGN KEY(plant_id_fk) 
REFERENCES plant(plant_id) ON DELETE SET NULL; 

JOINing the tables looks like:

SELECT p.taxon_name
    , p.common_name
    , hr.*
FROM plant p
    INNER JOIN harvesting_req hr ON p.plant_id = hr.plant_id;

The output looks like:

taxon_name common_name harvesting_req_id plant_id number_of_harvests descriptive_growing_season min_length_of_growing_harvest max_length_of_growing_harvest harvest_start_date
Taraxacum dandelion 1 1 2 2021-04-15
Taraxacum dandelion 2 1 3 2021-07-01

I'm not convinced you need a number_of_harvest column in the harvesting_req table since you'd have a row per harvest.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.