Best way to link multiple items to individual ones in MySQL

Question

I'm making a site with PHP and MySQL. The big idea is that you can register and you can add objects to your profile. You can have only 1 profile, you can have multiple items(infinite, in theory) and each items can have multiple pictures(infinite, in theory).

I'm wondering how to best implement this in MySQL. I've had a very basic education on MySQL, in that I know how to make a table, what the various datatypes are, but things like foreign keys are something that's buried in the back of my memory. I looked it up, but the understanding is coming a bit slow.

I could simply make 3 tables:

1. Users_name + User_ID
2. Item_ID + User_ID
3. Item_ID + Picture_ID

Downside is that for every single search action, or profile view or even item view, I'd be querying the entire contents of the two last tables, which seems like overkill.

Can someone help me here?

Answer 1

Is it fair to assume that any one item can only be related to one user? If so, your solution is correct. A one-to-many relationship should be defined with the one (users) listed in one table and the many (items) listed in another but identified using the many's id and the one's id.

Because by doing this you have limited the rows SQL must search to find the, say, item related to a user, your queries should be relatively quick.

In short, you are correct.

Answer 2

Your schema is actually pretty good. Now, your fear is that you'd query the entire contents of the tables. This is very much not true, given a good index on your primary and foreign keys!

select
    u.users_name,
    i.itemid
from
    users u
    inner join items i on
        u.user_id = i.user_id
where
    u.user_id = 1234

That rowset would return very quickly, even with hundreds of thousands of rows! The reason is because the query would use an index seek to find the row in users where that user_id is. Likewise once you join it to the items table. Therefore, it's just two very quick lookups to find all of the items you're looking for, and it won't ever have to scan the tables.

SQL is absolutely optimized for this stuff. Please take advantage of it!