CODEWITHSUNDEEP
phpmysqlrelational-databaserelationship
Imnotapotato
Imnotapotato

Reputation: 5808

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

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?

Upvotes: 1

Views: 193

Answers (2)

Tomas Vysniauskas
Tomas Vysniauskas

Reputation: 126

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'

Upvotes: 3

DaveTheRave
DaveTheRave

Reputation: 463

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)..

Upvotes: 0

Related Questions