Normalise a small table. Should be easy for pros

Databases
1

Hi,

I would like to normalise the table shown below. At the moment, the table is limited to 3 photos for each user, but later on I may want to increase the photo limit to 6. What is the best way to normalise this table?

_Table not normalised
user_id (INT)
username (VARCHAR)
password (CHAR)
email (CHAR)
date (DATETIME)
photo1_url (VARCHAR)
Photo2_url (VARCHAR)
Photo3_url (VARCHAR)

Is the normalisation below ok? If each user has 6 photos and the table has 1000s of records, table 2 would be massive. Should I introduce a 3rd table some how?

TABLE 1
user_id (INT)
username (VARCHAR)
password (CHAR)
email (CHAR)
date (DATETIME)

TABLE 2
user_id (INT)
photo_url (VARCHAR)

2

I wouldn’t introduce a new table - it’s not needed. The only thing I might add is a unique identifier on the photo table, but that would only if the photo needed to be identified somewhere else. You could use a mixed key of userid + url, but it’s not critical.

3

^^ What Dave said

Just out of curiousity: how did you imagine a third table would diminish the volume of the data?

4

Thanks for your help guys!

All this normalisation stuff is new to me and I wasn’t sure if someone could make another table out of the unnormalised data.

I have one more question if you don’t mind. :slight_smile:

If I want to limit the amount of photos a user can upload, I will need to do it in PHP (or some other web scripting language). Is that correct?

So for example in PHP, I would first query the database to find the amount of photos stored under “user_id”. If it’s less than 3, I can upload a new photo. Or is there some other way I could limit it in sql?