MySQL table design practice: Create a picture management table and photo album table

MySQL table design practice: Create a picture management table and photo album table

In practical applications, picture management and photo album management are a common requirement. In this article, we will explore how to design and create a picture management table and photo album table through MySQL.

First, let’s create a picture management table. This table will store information related to the image, such as the image's ID, name, description, upload time, etc.

CREATE TABLE photos (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In the above example, we use the ID of the auto-increment attribute as the primary key. The name of the image is a non-nullable VARCHAR type. The description uses the TEXT type, which can store longer text content. The upload time uses the TIMESTAMP type, and the default value is set to the current time.

Next, let’s create an album table. The album table will be used to manage different picture albums. An album can contain multiple pictures.

CREATE TABLE albums (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In the above example, we also use the ID of the auto-increment attribute as the primary key . The name of the album is a non-nullable VARCHAR type. The album description also uses the TEXT type. The creation time also uses the TIMESTAMP type, and the default value is set to the current time.

Next, we need to create an intermediate table to establish the relationship between the album and the picture. This table will store the IDs of albums and pictures, and is used to associate multiple pictures into one album.

CREATE TABLE album_photos (
album_id INT,
photo_id INT,
PRIMARY KEY (album_id , photo_id),
FOREIGN KEY (album_id) REFERENCES albums (id) ON DELETE CASCADE,
FOREIGN KEY (photo_id) REFERENCES photos (id) ON DELETE CASCADE
);

In the above example, we use Album ID and picture ID are used as joint primary keys. Through foreign key constraints, we ensure that when the album or picture is deleted, the related associated data will also be deleted.

Now, we have successfully created the picture management table and album table, and established the association between the album and the picture. In practical applications, we can add, delete, modify, and query these tables as needed to implement the management functions of pictures and photo albums.

For example, if we want to get a list of all pictures, we can use the following SQL query:

SELECT * FROM photos;

If If we want to get all the pictures in an album, we can use the following SQL query:

SELECT photos.* FROM photos
JOIN album_photos ON photos.id = album_photos.photo_id
WHERE album_photos.album_id = Album ID;

Of course, in addition to the basic operations of adding, deleting, modifying and checking, we can also add more functions according to actual needs. For example, you can add other fields such as image tags and views, or you can add fields such as album cover images.

Summary:
Designing and creating a picture management table and album table through MySQL can help us better manage and organize picture resources. Through appropriate table design and association relationships, we can easily implement the management functions of pictures and albums. I hope this article will be helpful to your table design in practical applications.

The above is the detailed content of MySQL table design practice: Create a picture management table and photo album table. For more information, please follow other related articles on the PHP Chinese website!