How to store a lot of data (more than 3000 rows) for one row in a table (MySQL DB)?

Question

I want to store a lot of data (dates) for one row in my database, what is the right way to do that?

I will give an example:

If I have a variaty of fruits, and I want to count during the month, how much fruits I ate, and how much fruits my ate.

This is what I think of doing right now and I'm not sure this is the right and best way:

"Fruit" Table: f_id, f_name (fruit name), f_data (fruit count for each day).

Then create a new table called

"Data": dat_id, dat_date, dat_count, dat_friend_count.

and connect the unique fruit_id with dat_id. (1 : n)

and whenever I want to get for example: A display of all the apples I ate -> and apple ID is 7, I look up the ID number 7 in the "Data" table, and display them for the user with the dates.

Is this the right way working with this kind of data / table structure?

What is "a lot" of data? There are practical limits on row length in MySQL — Jay Blanchard
– Jay Blanchard, Commented Dec 21, 2015 at 14:47
monthly data - for each fruit. I have a lot of fruits. around 1000 fruits. I eat a lot. — Imnotapotato
– Imnotapotato, Commented Dec 21, 2015 at 14:51

Tomas Vysniauskas · Accepted Answer · 2015-12-21 15:35:58Z

No, from relational perspective, your example is not correct.

You should have two tables:

Table "Fruit" id | fruit_name

(this is where you store information about what kind of fruits you have)

Table "Eat" id | fruit_id | fruit_count | date

(this is where you record all your actions, i.e. every single time you ate a fruit - when and how many)

Then, to find out how many fruits in total did you eat in December 2015, you would write a SQL query:

SELECT COUNT(eat.fruit_count) FROM fruit LEFT JOIN eat ON eat.fruit_id = fruit.id WHERE eat.date >= '2015-12-01 00:00:00' AND eat.date <= '2015-12-31 23:59:59'

To find out how many fruits did you eat for one specific kind of fruit, you should add the ID of Fruit in WHERE clause:

SELECT COUNT(eat.fruit_count) FROM fruit LEFT JOIN eat ON eat.fruit_id = fruit.id WHERE eat.date >= '2015-12-01 00:00:00' AND eat.date <= '2015-12-31 23:59:59' AND fruit.id = '1'

I understand. So it's almost like my example, just with one more column of 'fruit_id' in the "eat" table which connects the with the 'id' in the "fruit" table.

DaveTheRave · Accepted Answer · 2015-12-21 15:31:19Z

Without knowing alot about your problem. I would have three tables..

1) A fruit table.. that describes all the fruits

idfruit, fruitname, fruitdescription

2) A person table.. that describes people

idperson, personname, along with any other attributes as additional columns,

3) A fruit tracker table that tracks each instance of eating fruit

idfruit, idperson, eatdatetime.

That should be enough information to find out who ate what when. without mixing your actual data with your meta data (persons and fruit)..

Collectives™ on Stack Overflow

How to store a lot of data (more than 3000 rows) for one row in a table (MySQL DB)?

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related