How to add a list to a SQL database (using java)

Question

I am new to sql and I was wondering how i can add a list to a database. Here is what I mean:

I have an a java class like this that i want to insert its info into a db:

public class Dog{

  private int dog_id;
  private String dog_name;
  private ArrayList<String> dog_friends;

}

Every dog object will have a different amount size of friends. My question is how i can add this list to the db. I was planning on having 3 columns (id,name,list), I just dont know how to add a list.

Answer 1

What you have is, in the relational database world, called a one-to-many relationship, and it requires two database tables to represent the information.

In your first table, you'll have id and name; in your second table you'll have dog_id, friend_id and dog_friend.

There will be possibly many entries in the second table for every entry in the first table. When you insert a row in the second table, be sure to set dog_id to match the id in the first table.

With this structure, you can use JOINs in your SQL query to get all the information at once, or query the tables independently, depending on your needs.

Answer 2

Your table structure should look like this (ish):

dogs
id: int(11)
name: varchar(255)

dog_friends
dog_id: int(11) 
dog_friend_id: int(11)

You don't need a dog_friend and a friend_id in the second table, assuming dog_friends are other dogs. Both dog_id and dog_friend_id will be references to the ID field in the dog table.

However, @Mark Elliot is correct in that you will use JOINs to access the data.

Also, you should read up on relational databases, in general, before venturing too far into SQL. Learning WHY is better than learning HOW. Hope this helps!