I'm new to MySQL but have a pretty solid background in a wide variety of programming languages.

Right now I'm designing my first database from the ground up. I understand the basic functionality of MySQL tables and what a relational database is but I'm having trouble wrapping my head around a few things so I'm posting this for help (searching hasn't worked, the terms I've been using are too general and I don't know how to narrow it down). Here's where I'm stuck:

I want to pull Facebook data, specifically interests ("likes") and user location.

If this were some OO language I would just create a data structure for Users with all of the info in it (Facebook ID, interests as an array, location) but I'm not sure how to recreate this in MySQL.

Right now I'm thinking of creating a users table with

• Facebook ID (primary key?)
• Name
• Location
• Join date

Then creating an interests table with

• Interest name (index, sorted alphabetically?)
• Maybe a count of users with this interest
• Foreign key that relates back to each user

I think this is where my lack of understanding comes in. How do I replicate the concept of a list or array in MySQL in a way that allows me to map each interest to each user who has "liked" that interest?

Any other suggestions, thoughts, or directions to good tutorial sites are greatly appreciated. I'm a tactile learner so getting my hands dirty with a tutorial would be great for me, I just haven't found one that covers this yet.

You could use a third table that would link the interests table to the user table. There would only be a record if the user liked that interest:

Table User_Interest:

Interest_ID
User_ID

To get a list of a user with all of their interests:

SELECT u.Name, i.Interest_Name
FROM Users AS u
INNER JOIN User_Interest AS ui ON ui.User_ID = u.ID
INNER JOIN interests AS i ON i.ID = ui.Interest_ID
WHERE u.Name = 'Tom Jones'

To get a list of a particular interest and all users that liked:

SELECT u.Name, i.Interest_Name
FROM Users AS u
INNER JOIN User_Interest AS ui ON ui.User_ID = u.ID
INNER JOIN interests AS i ON i.ID = ui.Interest_ID
WHERE i.Interest_Name = 'Hiking'

This type of setup is called a "many to many" relation. To do this, you will need 3 tables. 2 tables which contain your data and the last table a mapping table.
Here is an example of a many to many relation ship where the two objects are Students and Courses

Example

Your user table looks like this:
(ID_User, User_Name, ...)

Your interest table looks like this:
(ID_Interest, Interest_Name, ...)

Now your mapping table will look like this:
(ID_User, ID_Interest)

Sample Data

Now lets put some data in the tables

User_Table
------------------------
ID_user | Username | ...
------------------------
   1       John  
   2       Mark 
   3       Foo
   4       Bar

Interest_Table
----------------------------------
ID_Interest | Interest_Name | ... 
----------------------------------
   001         Pop
   002         Rock
   003         Alternative
   004         Rap

User_To_Interest (Mapping Table)
---------------------------
ID_user | ID_Interest
---------------------------
  1          001
  1          003
  3          002
  2          004
  4          001

Examining this

Ok so here if you analyze what has been set up, we are mapping users to interests. The mapping table sort of adds wires from the User object to an interest object.