DB design

I have a total newb question about DB design;

What is best practice for this scenario -

Two tables (dumbed down scenario):

User Table
userID
name
description
images *

Images Table
imageID
title
description
path

* For the User Table -> [images] field; I want the user to be able to have multiple images related to them.

Is it standard / best practice to have that field contain all the imageID's as such "101, 102, 103, 104...ect"? Then once that query is complete, then do an independent query to get all the specific details about each image per their respective ID?

If not, how does one set up where one table relates to multiple of another?
Thanks in advance.
-MT

 

If each image is associated to a user and users can have multiple images then you would most likely remove the "images" from the UserTable and add the UserID field to the ImagesTable.

Depending on what data you are wanting, how you are displaying things on a page, how many images users will have etc will dictate the best way to do the queries. If you simply want all the images for the a certain user it would be:

SELECT title, description, path FROM ImagesTable WHERE UserID = x;

Code (markup):

 

Astaroth Solutions,
What if multiple users could select the same image? How would I separate who's ID would fill that specific field?

Should I create another table just for a relation between UserTable and ImagesTable and the respective fields would be something like;

UserImage Table
userImageID
userID
imageID

Then do a join on this when looking up users' respective images?

Thanks for helping.
-MT

 

Yes, if it is a many to many relationship then a third table.

 

Awesomeness! Thanks again for the help. Yeah I did some reading up on some things after my original post over the weekend. I found a couple stellar articles that helped a lot and of course here on the forums. Thanks again.
-MT