Re: How to store user's tastes

From: J.O. Aho <user_at_example.net>
Date: Mon, 22 Apr 2019 10:28:56 +0200
Message-ID: <gi5ca8F23j2U1_at_mid.individual.net>

On 22/04/2019 00.21, ^Bart wrote:

>> More difficult to keep the data consistent over tables.

>
> I created three tables:
>
> - ingredientscategories
> values Meat, Fish, Cereals, Cereals gluten free, Vegetables, Fish, etc.
>
> - ingredients
> values every ingredients
>
> - foodlists
> values Celiac, Vegan, etc.
>
> Foodlists table is a deny table, a Vegan, Vegetarian, etc. will be set
> like "Style" and Celiac will be set like "Intollerance" something like
> not dangerous and dangerous for example for the restaurant's owner!
>
> CREATE TABLE ingredientcategories
> (
> id_ingredientcategory TINYINT(7) NOT NULL AUTO_INCREMENT,
> name VARCHAR(30) NOT NULL,
> PRIMARY KEY (id_ingredientcategory)
> )
> ENGINE=INNODB;
>
> CREATE TABLE ingredients
> (
> id_ingredient SMALLINT(7) NOT NULL AUTO_INCREMENT,
> name VARCHAR(30) NOT NULL,
> FK_id_ingredientcategory TINYINT(15) NOT NULL,
> PRIMARY KEY (id_ingredient),
> INDEX (FK_id_ingredientcategory),
> FOREIGN KEY (FK_id_ingredientcategory) REFERENCES ingredientcategories
> (id_ingredientcategory)
> )
> ENGINE=INNODB;
>
> CREATE TABLE foodlists
> (
> id_foodlist BIGINT(7) NOT NULL AUTO_INCREMENT,
> name VARCHAR(50) NOT NULL UNIQUE,
> FK_id_ingredientcategory SMALLINT(7) NOT NULL,
> kind ENUM ("Style","Intollerance"),
> PRIMARY KEY (id_foodlist),
> INDEX (FK_id_ingredientcategory),
> FOREIGN KEY (FK_id_ingredientcategory) REFERENCES ingredientcategories
> (id_ingredientcategory)
> )
> ENGINE=INNODB;
[Quoted] Won't you need a dish table and a join table (as a dish can have multiple ingredients and an ingredient can be part of multiple dishes)?

[Quoted] Also as Luuk pointed out, you need to have the same type for the same key over every table, and the tiny int will be too small, go for int instead.

[Quoted] id_ingredientcategory TINYINT(7)/TINYINT(15)/SMALLINT(7)

tinyint  signed:127 		unsinged:255
smallint signed:32767 		unsigned:65535
int	 signed:2147483647 	unsgined:4294967295

Usually for key's you should pick unsigned as you otherwise waste half of the key id's.

-- 

  //Aho

Received on Mon Apr 22 2019 - 10:28:56 CEST