ArrayList Database MySQL

Question

Have very limited experience with databases and trying to figure out the best method for creating a growing database of users and keeping track of their growing information when added.

For instance, I have 3 users - Apple, Banana, Cherry. And they all have a list of information associated with them.

  Apple: [1/1/17, green, 3], [2/4/17, red, 5], [4/19/17, blue, 2]
  Banana: [2/18/17, yellow, 6], [5/14/17, yellow, 5] 
  Cherry: [5/21/17, green, 12], [8/2/17, red, 5], [9/29/17, blue, 42], 
  [1/1/17, green, 3]

Before even getting into working with a growing list, how does this get applied to a database?

Do I create a main database, something like FruitDatabase, and then inside those databases have Apple, Banana, and Cherry as their own tables?

Or does each user get their own database and then their associated information get applied to their own table (that doesn't seem efficient)?

What if I later want to add up all of Apple's number (3+5+2), what's the best method of doing that? It doesn't seem like I can put an ArrayList into a database, do I just convert it into a string and separate the number with a comma? And if I want to add a new user, DragonFruit, is it more efficient to add it as a column or row?

Ravi Chandra · Accepted Answer · 2017-06-05 06:02:49Z

1

You can create tables like the following to represent your data.

create table fruit_type (
  fruit_type_id int,
  name varchar(20),
  color varchar(10),
  primary key(fruit_type_id)
);

create table fruits (
 fruit_id int,
 date_added date,
 count int,
 foreign key (fruit_id) references fruit_type(fruit_type_id)
)

Before inserting data into fruits table, you need to have a row for each fruit type in fruit_type table.

answered Jun 5, 2017 at 6:02

Ravi Chandra

68712 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rakshit Singh · Accepted Answer · 2017-06-05 05:44:56Z

0

The way this works is that you should have 1 database/schema for your entire application say Fruit_Management. Within that you should have one table called Fruits. This Fruits table will have 5 columns namely id, type, date, colour and qty. which will map to 1, Apple, 1/1/17, green, 3. Now if you want to get the total number of apples you write aggregation queries like select count(*) from Fruits where Fruits.type='Apple'.

This way your schema remains flexible and normalized.

answered Jun 5, 2017 at 5:44

Rakshit Singh

872 silver badges12 bronze badges

Collectives™ on Stack Overflow

ArrayList Database MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related