Re: How to store user's tastes

From: J.O. Aho <user_at_example.net>
Date: Sun, 21 Apr 2019 23:10:33 +0200
Message-ID: <gi44i9Fol19U1_at_mid.individual.net>

On 21/04/2019 20.07, Luuk wrote:

> On 21-4-2019 19:07, ^Bart wrote:

>> Hello everybody!
>>
>> I need to create a DB where I should store user's tastes or user's
>> foodstyle (vegan, vegetarian, etc.)
>>
>> CREATE TABLE foodstyles
>> (
>> id_foodstyle BIGINT(7) NOT NULL AUTO_INCREMENT,
>> name VARCHAR(50) NOT NULL UNIQUE,
>> FK_id_ingredient SMALLINT(7) NOT NULL,
>> PRIMARY KEY (id_foodstyle),
>> INDEX (FK_id_ingredient),
>> FOREIGN KEY (FK_id_ingredient) REFERENCES ingredients (id_ingredient)
>> )
>> ENGINE=INNODB;
>>
>> In this case I should repeat the name vegan a lot of times:
>>
>> name   | FK_id_ingredient
>> vegan  |       4
>> vegan  |       2
>> vegan  |       8
>> vegan  |       9
>> vegan  |       3
>>
>> How could I find the best way to store informations in a group named
>> vegan with a lot of things?
>>
>
> What is the problem with storing 'vegan' a lot of times?

To know which options the data input person can select from you need to do a "select name from foodstyle group by name" which in most cases would lead to "Explain: Using temporary; Using filesort", not as effective as using index.

[Quoted] More difficult to keep the data consistent over tables.

...

> You could also create an id for it, but then you would be storing the id > which refers to 'vegan' a lot of times.

[Quoted] Takes less disk space to store the id than the text.

 > (and have to find out which id
 > it is...)

Which is quite small thing to fix misspellings

-- 

  //Aho

Received on Sun Apr 21 2019 - 23:10:33 CEST