PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Mon Aug 21, 2017 7:12 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 13 posts ] 
Author Message
PostPosted: Tue Aug 08, 2017 11:15 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 110
Hi,

im trying to design a small food database. I want to store info such as
product name (UNIQUE)
manufacturer
nutritional info
ingredients

Product name and maufacturer I can save as varchar.
For the nutritional info I thought of separate columns such as carb, fat, protein, energy (INT or float 4,2)

The thing is I do not know how to save ingredients in the best way.
First: I don't know how many ingredients there will be in different foods.. one might have 3 ingredients and another might have 40 ingredients...
Second: I want to be able to quickly run through the database and sort out all products that contain for example EGG.

I thought of saving the ingredients in a text file separated by some special char such as | so I can turn em into an array and compare that array with an array of stuff I want to sort out but I don't know if there is any better way of doing this?

Example ingredient(text):
egg|oil|milk|citric acid|fish ->Load into array1
ex filter(text)
egg|E331|apple ->Load into array2

compare array1 and array2 if they share some common value (in this case egg)

Any ideas how to best store the food in a database (so it will be fast to search even if there are many users)?


Top
 Profile  
 
PostPosted: Tue Aug 08, 2017 4:44 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6493
Location: WA, USA
What database system?

If you're putting stuff into a database then don't put stuff into a file too. That defeats the whole purpose of using a database.

Rule of thumb: Once set up, a database structure shouldn't need to be altered in the normal course of using it. Obviously, it's okay to modify if you need to add or remove behavior.
Rule of thumb: Don't use multiple columns for data when you think you might need to add or remove them over time. If you have carbs, fat, protein, and calories now, then decide you want trans and sat fat separate, that means you'd have to alter the structures and that's not a good sign.

You need to figure out sort of data you're storing. If you don't know what nutritional info you want then you need to know what type of info it will be. For a start, you have "a name for the particular piece of information" (carbohydrates, fat, protein, etc.) and "the amount of it in the food" (eg, X mg or Y g). That is storable: use one table to track the sorts of information available and another to store values for each particular piece of food. Two tables means (a) you don't keep repeating names like "carb" all over and (b) you can use the first table as a list to work from when entering data, which probably won't be expanded often.

Same process for ingredients but even more obvious: you can't possibly have a column for each ingredient (what would you even store in it?) but you can have a table containing "name of ingredient". You could also have a second table with the names of ingredients, so the second is the food and which ingredients are contained, but that might not be worth it in this case since it would be expanded often.

Here's an example with a can of soup I have lying around.
Code:
products

id | name                                               | manufacturer | serving size | servings
---+----------------------------------------------------+--------------+--------------+---------
1  | Progresso Chicken Corn Chowder Flavored with Bacon | Progresso    | 1 cup (255g) | about 2

nutritionalinfo_list

id | name                | unit
---+---------------------+-----
1  | Calories            |
2  | Calories from Fat   |
3  | Total Fat           | g
4  | Sodium              | mg
5  | Total Carbohydrates | g
6  | Sugars              | g
7  | Protein             | g

nutritionalinfo

product | info | amount
--------+------+-------
      1 |    1 |  190.0
      1 |    2 |   70.0
      1 |    3 |    8.0
      1 |    4 |  870.0
      1 |    5 |   23.0
      1 |    6 |    5.0
      1 |    7 |    6.0

ingredients

product | name
--------+---------
      1 | chicken broth
      1 | corn
      1 | cooked white chicken meat
      1 | potatoes
      1 | celery
      1 | ...


But searching ingredients may not be as easy as you think. If you want to search for "egg" then what about "eggs"? What about "egg yolk"? And on the other hand, should a search for "chicken" also match "chicken broth"?


Top
 Profile  
 
PostPosted: Tue Aug 08, 2017 7:03 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13421
Location: New York, NY, US
I agree with requinix that a database with relations is the way to go. I am less concerned about adding columns that requinix is, so I would put all the nutritional info in the product record because there is a 1:1 relation between the info and the product. And since products and ingredients are all foods, you could put them all in one table for simplicity. I may simplify editing as well. The difference between products and ingredients is that products have a manufacturer. Ingredients probably need to be in some base serving size that can be multiplied by the amount in the products.

Then all you need a link table that give you the ingredients for each product.
Code:
foods

id | name                                          | manufacturer | serving size | servings | calories | fat | protein | sugars | ...
---+----------------------------------------------------+--------------+--------------+---------
1  | Progresso Chicken Corn Chowder  | Progresso   | 1 cup (255g)  | about 2 | ...
2  | Chicken broth                                 |               |  1g      |
3  | Corn                                          |               |  1g
4  | Cooked white chicken meat
5  | Potatoes
6  | Celery
7  | ...

food_ingredients

productID | ingredientID | amount
--------+------+-------
      1 |    2 |   70.0
      1 |    3 |    8.0
      1 |    4 |   870.0
      1 |    5 |   23.0
      1 |    6 |    5.0
      1 |    7 |    6.0

_________________
(#10850)


Top
 Profile  
 
PostPosted: Wed Aug 09, 2017 11:57 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 110
Thanks guys!

Requinix
I use Mysql (MariaDB) InnoDB

I already thought of searching ingredients and came up with that will probably be very hard so I worry about that later haha.

((I thought of having some ingredient database that contain only the singular form of the word (egg) so if you type eggs you will get a suggestion for egg, egg white or egg yolk to chose from or something like that..))


First I thought I'd go with Cristophers 'idea' to have more columns in the food table (fat, carb..) since that is unlikely to change (this is how i set it up now)..but the more i think of it the more i like the idea of having a table hold the names and one table for the values.. that way if I want to add sodium later on for example it will be pretty easy.

The "bad thing" is the DB will not be very readable for humans if something goes wrong heh

I also wonder what will happen with the performance over time if producers change ingredients so the info becomes defragmented.

Say I have 30 000 food products and the food ID=1 changed a couple of times over say 5 years:

Ingredient Table
(Table row) Product : Name
1 1 Oil
2 1 Pepper
3 1 Salt
4 2 Garlic
5 2 Milk
.
.
.
10000 987 Cheese
10000 1 Tuna
10000 988 Salt
.
.
670000 1 Tomato

Will it matter at all?

Ok I'm off to remodel my tables.. Thanks again :)


Top
 Profile  
 
PostPosted: Wed Aug 09, 2017 9:59 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6493
Location: WA, USA
With proper indexes it doesn't matter. Databases are more powerful than you give them credit for.


Top
 Profile  
 
PostPosted: Thu Aug 10, 2017 9:29 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 110
Cool thanks :)

See edit below______________________________________________
Another designquestion.. If I want to connect this database to an API (JSON) which would be the "best way" to handle insert, update and get requests.

I was thinking of making a product class with all variables I want to keep track of like energy, fat and so on and then in the class make functions like:

AddProduct(){
Adds the product to DB
}

GetProduct(){
Get product from DB
}
and so on..

The other idea is to put the code in the API as an endpoint
Endpoint Product
If Method == 'GET' {
Code to get product from DB
} else if Method == 'POST' {
Code to add product to DB
}


I know I need some API code even if I make product as a class but then it would look more like
If Method == 'GET' {
$p = new Product;
$p->GetProduct();
} else if Method == 'POST' {
$p = new Product;
$p->AddProduct();
}

What would you recommend and why?

EDIT: Well, I was eager to get going so I made a product class where I can hide all DB code not to clutter the API class too much.


Maybe my brain is too tired now but I cant figure out how to best store the nutritional info inside the class... (Ingredients for example I store as a simple array ([0]=>Milk [1]=>Salt [2]=>Corn syrup...)
I figure I need to store it in an array somehow but don't know how to code it in a good way to read from both tables...

from my product class where I store ingredients in the array:
Syntax: [ Download ] [ Hide ]
 if ($stmt = $mysqli->prepare("SELECT
                            Name
                            FROM API_Product_Ingredient_SWE where Product = ?"
)){
                                $stmt->bind_param('i', $this->id);
                                $stmt->execute();
                                $stmt->bind_result($name);
                                $stmt->store_result();
                                while($row = $stmt->fetch()){
                                   $this->ingredients[] = $name;
                                }
                            }
 


For the nutritional I guess I will have a similar code for the table that holds the nutritional amount for each product but how do i get the stuff from nutritional_list (the names (like fat, carbs and so on) and the unit (like gram or mg)) into the mix... Shall i read the nutritional list table from within the while {} loop? (my gut feeling is that it will be slow to do so).. is there a better way?

Thanks :)

/Edit__________________________________________________


Top
 Profile  
 
PostPosted: Thu Aug 17, 2017 9:45 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 110
Ok,

after some trial and error, more error,even more error ...... and some more errors I came up with:

Syntax: [ Download ] [ Hide ]
if ($stmt = $mysqli->prepare("SELECT
                            API_Nutrition_List.Name, API_Nutrition_List.Unit, API_Nutrition.Amount
                            FROM API_Nutrition_List, API_Nutrition
                            WHERE API_Nutrition.Product = 1 AND API_Nutrition.List = API_Nutrition_List.id"
)){
                                //$stmt->bind_param('i', $this->id);
                                $stmt->execute();
                                $stmt->bind_result($name, $unit, $value);
                                $stmt->store_result();
                                while($row = $stmt->fetch()){
                                   $this->nutritionlist[$name] = $value.$unit;
                                }
 


the product = 1 is just for testing should be ?

It seems to work but is this the best way to do it?


Top
 Profile  
 
PostPosted: Fri Aug 18, 2017 2:57 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6493
Location: WA, USA
So here's the way these sorts of things are generally constructed:

1. A class for the object as it exists in the database ("database model")
2. A class for the object as it needs to be used by your application ("business model")
3. A class for the "page" that handles a request ("controller")

1 and 2 are often combined into one class, which is fine. Having them separate means the database portion is easily managed and the business portion can do whatever you want it to do (and I recommend this) but having them combined means less code to manage and it can be easier to understand.

In your case,

Each API endpoint is represented by one controller. An "endpoint" is often considered to be a distinct URL, even that covers multiple actions. REST APIs (the standard way to do an API) have four basic actions: create, read, update, and delete (known together as "CRUD"). In code, controller classes normally have one method per verb: GET (read) or POST (create, update, delete), though the fancier APIs will specifically use PUT (create) and DELETE (delete).

Assuming you don't want to delete products (which needs its own discussion), I count four different APIs:

1. Get/search all products (you may or may not want this)
2. Create new product
3. Get one product
4. Update one product (if one can create products then updating is probably a good idea too)

Syntax: [ Download ] [ Hide ]
abstract class ApiController {

        protected function notsupported() {
                http_response_code(405);
                exit;
        }

        public function get() { $this->notsupported(); }
        public function post() { $this->notsupported(); }

        public function execute() {
                if ($_SERVER["REQUEST_METHOD"] == "GET") {
                        $this->get();
                } else if ($_SERVER["REQUEST_METHOD"] == "POST") {
                        // everybody uses json nowadays
                        if (isset($_SERVER["CONTENT_TYPE"]) && strncmp($_SERVER["CONTENT_TYPE"], "application/json", 16) == 0) {
                                $_POST = json_decode(file_get_contents("php://input"), true);
                        }
                        $this->post();
                } else {
                        $this->notsupported();
                }
        }

}

// like /api/product -> /api/product/index.php
class ProductIndexApiController extends ApiController {

        public function get() {
                /* return all products? */
        }

        public function post() {
                /* create a new product */
        }

}

// like /api/product/123 -> /api/product/product.php?id=123
class ProductApiController extends ApiController {

        public function get() {
                /* locate product with $_GET["id"] and return */
        }

        public function post() {
                /* locate product with $_GET["id"] and update, or something */
        }

}
 


For the database portion, personally I structure my classes like this:

Syntax: [ Download ] [ Hide ]
class Product {

        public $id;
        public $nutritionlist;

        public function __construct(array $data = []) {
                if ($data) {
                        $this->id = $data["id"];
                        $this->nutritionlist = $data["nutritionlist"];
                }
        }

        public static function get($id) {
                /* get $data from database */
                /* also get the ingredients and put it into $data["nutritionlist"] */
                return new self($data);
        }

        public function save() {
                if ($this->id) {
                        /* update... */
                } else {
                        /* insert... */
                }
        }

}
 


Then you use it in a controller like
Syntax: [ Download ] [ Hide ]
// get
$product = Product::get($_GET["id"]);
return json_encode($product);

// update
$product = Product::get($_GET["id"]);
/* update $product */
$product->save();

// create
$product = new Product();
/* set $product accordingly */
$product->save();
 


Note that the Product class doesn't do anything with $_GET or $_POST: it is a database model so it only cares about getting data from the database and putting data into it. The controller class is what deals with each API request so it uses $_GET/POST, and it uses Product instead of going to the database directly.

And another comment: when doing stuff in an API, try to keep the data as it is represented. What I mean is I see you doing the $value.$unit with the nutritionlist. That's okay when presenting stuff to a user, but with an API you should keep the two separate. It's okay for a product to look like
Code:
{
   "id": 1,
   "nutritionlist": {
      "fat": {
         "value": 1,
         "unit": "g"
      }
   }
}

and it's better than
Code:
{
   "id": 1,
   "nutritionlist": {
      "fat": "1g"
   }
}

The problem with combining it is that you have to combine the value and units everywhere, but more that you also have to split them apart when taking in data through the API. That latter half is annoying.


Anyway, yes, you can do product = 1 for testing, but why bother? It's only a little more code to make it work correctly.


Top
 Profile  
 
PostPosted: Fri Aug 18, 2017 10:54 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 110
Thanks !

For now I use http://coreymaynard.com/blog/creating-a-restful-api-with-php/ as a template for my API and trying to figure out a good token system :) I made a post about it in the code section.

I thought of separating the DB model and the Bussiness model but Im still a noob so I figured having just one class is easier for me to follow as of now. But I really like how you structured your classes so maybe Ill rewrite my code when I get it to work (..as I want heh).

Quote:
And another comment: when doing stuff in an API, try to keep the data as it is represented.

Yeah, I was going back and forth with this one. I thought since I know the Unit I'd trim the input to remove g or mg.. before saving but Ill change it.

Quote:
Anyway, yes, you can do product = 1 for testing, but why bother? It's only a little more code to make it work correctly.


Yeah I know, it wasn't a question even though it looked like it since I ended with ? I just wanted to point out that for my testing i had replaced the ? with a 1 so you wouldn't point out to me that what I wrote would only display the first product in my DB no matter what.. :D

Anyway big thanks for taking the time to help me, it's very appreciated :)


Top
 Profile  
 
PostPosted: Fri Aug 18, 2017 7:43 pm 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 110
Another question...

Syntax: [ Download ] [ Hide ]
 public static function get($id) {
                /* get $data from database */
                /* also get the ingredients and put it into $data["nutritionlist"] */
                return new self($data);
        }


If I have information about the product in say 4 different tables that I want to read into the array is it faster to read table for table like

Syntax: [ Download ] [ Hide ]
 if ($stmt = $mysqli->prepare("SELECT
                        name
                        FROM Table1
                        WHERE id = ?"
)) {  
                        $stmt->bind_param('i', $id);
                        $stmt->execute();
                        $stmt->bind_result($data["name"]);
                        $stmt->store_result();
                        $stmt->fetch();
                    }

if ($stmt = $mysqli->prepare("SELECT
                        Manufacturer
                        FROM Table2
                        WHERE id = ?"
)) {  
                        $stmt->bind_param('i', $id);
                        $stmt->execute();
                        $stmt->bind_result($data["manufacturer"]);
                        $stmt->store_result();
                        $stmt->fetch();
                    }

and so on....
 


or will it be faster using aliases and put all requests and tables in the same query?

Syntax: [ Download ] [ Hide ]
if ($stmt = $mysqli->prepare("SELECT
                            Table1.Name, Table2.Manufacturer
                            FROM Table1, Table2
                            WHERE id = ? "
)){
                                //$stmt->bind_param('i', $id);
                                $stmt->execute();
                                $stmt->bind_result($name, $manufacturer);
                                $stmt->store_result();
                                $stmt->fetch();
 


Top
 Profile  
 
PostPosted: Sat Aug 19, 2017 2:46 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6493
Location: WA, USA
I'd use one query. I would also try to consolidate all those tables, if not into one table (obviously best) then into one view (easy but cannot write to).


Top
 Profile  
 
PostPosted: Sat Aug 19, 2017 5:45 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 110
Well I have:
1 table that holds the connection between a barcode and product id.
1 table that hold general product info
1 table that hold manufacturer information
1 table that holds ingredients
1 table that holds allergen info (may contain traces of) that works in the same way as ingredients
1 table that holds nutrition list
1 table for nutritional info.

The reason I did it this way is I want to be able to quickly search between barcode and id (both ways) and in the future allow for different barcode tables (EAN13, EAN128..and so on)

Many products share the same manufacturer so I thought it is unessecary to repeat the info over and over.

Ingredients and nutritionlist/info(-I really liked this suggestion since I may want more nutritional data in the future) as per suggestion earlier in this post.
Allergens since it works in exacly the same way as ingredients (I guess i can merge these two but then I'd need another column to track if it's an ingredient or if it just may contain traces)...


Top
 Profile  
 
PostPosted: Sat Aug 19, 2017 9:10 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6493
Location: WA, USA
The question is how many rows there will be per product.
- If there's always one then it should be merged into the product table
- If there's optionally one then it depends on the data and how much there is
- If there's zero or more then it should remain as its own table

Based on the query earlier, which searches using the product ID, the manufacturer information would be the first case. If, however, you were to have a table of manufacturers, then the product table should have the manufacturer ID (assuming there's only one) and you would do a JOIN to get the manufacturer name - like
Syntax: [ Download ] [ Hide ]
SELECT products.name AS product, manufacturers.name AS manufacturer
FROM products
JOIN manufacturers ON products.manufacturer_id = manufacturers.id
WHERE products.id = ?


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group