PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Fri Dec 06, 2019 6:38 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 29 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Tue Aug 08, 2017 11:15 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
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: 6617
Location: WA, USA


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

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13592
Location: New York, NY, US

_________________
(#10850)


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

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
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: 6617
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: 172
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: 172
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: 6617
Location: WA, USA


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

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172


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

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
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: 6617
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: 172
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: 6617
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  
 
PostPosted: Mon Aug 21, 2017 1:36 pm 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
Thanks again :)

Ok so I merged everything into one big table except nutrition_list+info, allergens and ingredients. I also skipped autoinc id for food and use GTIN as unique identifier. It's much better now so thanks again :)


Top
 Profile  
 
PostPosted: Wed Aug 23, 2017 2:03 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 172
Syntax: [ Download ] [ Hide ]
public function save() {
                if ($this->id) {
                        /* update... */
                } else {
                        /* insert... */
                }
        }
 


Is this a better way than just INSERT ... ON DUPLICATE KEY UPDATE ?

If I'm going to use this function with an API is it not better to have 2 separate functions one for save() that returns an error like PRODUCT ALREADY EXIST if you try to add the same product and then another separate function for update that loads the info alredy in DB so you can change it.

If not I fear this update function would be as 'bad' as a DELETE function since you just type in the product ID and it will update all fields with NULL?
I guess I could do some check if it was just one table but since the ingredient table is ID / NAME it will be impossible to know if the manufacturer removed an ingredient or if the user just forgot to type it in?

(I know if I preload the info it still can be messed with but atleast they have to do something active (delete the info in the field) to remove info).


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 29 posts ]  Go to page 1, 2  Next

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