What is this form of database called?

Question

I'm new to databases and I'm thinking of creating one for a website. I started with SQL, but I really am not sure if I'm using the right kind of database.

Here's the problem:

What I have right now is the first option. So that means that, my query looks something like this:

Click to copy

user_id      photo_id     photo_url
0            0            abc.jpg
0            1            123.jpg
0            2            lol.png

etc.. But to me that seems a little bit inefficient when the database becomes BIG. So the thing I want is the second option shown in the picture. Something like this, then:

Click to copy

user_id      photos
0            {abc.jpg, 123.jpg, lol.png}

Or something like that:

Click to copy

user_id      photo_ids
0            {0, 1, 2}

I couldn't find anything like that, I only find the ordinary SQL. Is there anyway to do something like that^ (even if it isn't considered a "database")? If not, why is SQL more efficient for those kinds of situations? How can I make it more efficient? Thanks in advance.

Answer 1

Your initial approach to having a user_id, photo_id, photo_url is correct. This is the normalized relationship that most database management systems use.

The following relationship is called "one to many," as a user can have many photos.

You may want to go as far as separating the photo details and just providing a reference table between the users and photos.

The reason your second approach is inefficient is because databases are not designed to search or store multiple values in a single column. While it's possible to store data in this fashion, you shouldn't.

If you wanted to locate a particular photo for a user using your second approach, you would have to search using LIKE, which will most likely not make use of any indexes. The process of extracting or listing those photos would also be inefficient.

You can read more about basic database principles here.