MySQL设计澄清

Question

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.

Answer 1

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'