Database setup for large scale projects

[Dan]

I’m new to php/mysql

I planning to make a project which may have many users. One part of the project is that users have multiple, even hundreds, of pictures to their name. I was wondering how I should set up the tables for this. Should I do one image table that has all users and all their photos on it such as…

       IMAGES

| user | imageID |

| dan | 20030492 |
| dan | 12344942 |
| dan | 22344442 |
| dan | 78998492 |
| dan | 78998992 |
| bob | 25674562 |
| bob | 26574492 |
| bob | 25675672 |
| bob | 67003092 |

or should I have a database for each user such as …

   user_images

imageID
20030492
12344942
22344442
78998492
78998992
25674562
26574492
25675672
67003092

of course these aren’t the only columns that are going to be in the table. right now I have 5.

I was thinking that the first option may get too hectic if I have hundreds of users and then hundreds of photos for each. but then again I don’t know if the second option is smart to do.

[Zyppora]

What kind of relationship is there between the users and the photos? Is it 1xN or MxN? If 1xN, so photos can only belong to 1 user, then you could make a photos table with a user column in it. If MxN, you’ll have to use a translation table, otherwise you’ll have a tough road to walk

[Dan]

I don’t really know what your asking : I’m new to mysql. the images have ids, which are relative to their date. such that Jan 02, 2008 = “20080102”. it goes year month day. However more than one user can have the same image id. Bob can have 20080101 and Dan can have 20080101. I don’t know if that had anything to do with what you were saying

[waylon999]

Hi,

I believe what Zyppora is asking is if each picture will be associated with only 1 user (1xN) or if each picture can be associated with multiple users (MxN).

If each picture will only be associated with 1 user, then you can just have an images table that contains the info about the image (id, location, etc) and a column that references a user id. You should not design the database in a way that you are storing redundant data. Meaning that if you are storing the users name, address, etc. over and over again, you should rethink the design. So for example, a table like:

IMAGES
| user | imageID | user_address

| dan | 20030492 | 123 fake st
| dan | 12344942 | 123 fake st
| dan | 22344442 | 123 fake st
| dan | 78998492 | 123 fake st
| dan | 78998992 | 123 fake st
| bob | 25674562 | 456 fake st
| bob | 26574492 | 456 fake st
| bob | 25675672 | 456 fake st
| bob | 67003092 | 456 fake st

Would be a bad design.

In this instance, it would be better to have a table of users:
USERS
| user | userID | user_address

| dan | 1 | 123 fake st
| bob | 2 | 456 fake st

and a separate table of images that references the users through a userID:
| userID | imageID |

| 1 | 20030492 |
| 1 | 12344942 |
| 1 | 22344442 |
| 1 | 78998492 |
| 1 | 78998992 |
| 2 | 25674562 |
| 2 | 26574492 |
| 2 | 25675672 |
| 2 | 67003092 |

This way you are not storing redundant info (like the address) multiple (or hundreds) of times.

Hope this helps.