Application Example of one-to-one relational database mode

In relational databases, one-to-many relationships are widely used, and many-to-many relationships are also used, while one-to-one relationships are rarely used, this article shows an example of a one-to-one relationship.

First, we have a database:

This is a simple commercial database, and now there is a demand: users are required to upload multiple images to demonstrate goods, shops, markets, and brands. How should we design it?

The first thing that comes to mind is to append an affiliated table to each table for special image storage. First, create a market photo table:

Next is the shop photo table:

You will immediately find that there is a lot of redundancy, and if you have added an online shop table, you have to add an online shop photo table for it. If you are not satisfied with the information recorded in the photo, to add an equal-width thumbnail field, you have to modify all the photo tables, which is undoubtedly painful.

In this case, we can consider using a picture as a single table for Unified Storage. Then, we can create a new ing table to store the ing of each table to form a many-to-many relationship:

In this way, we can store all kinds of information in the photo table without worrying about repeated efforts.

But I have to think about the relationship: A brand can have multiple photos, and a picture can be used by multiple brands.

This is not in line with our intention. Photos cannot be stolen by other brands, so the solution is to set the photos in the ing table as the primary key so that they will not be repeated, the relationship between the ing table and the photo table will automatically become one-to-one:

At this time, the relationship between the image and the brand becomes zero or one-to-many. The reason is zero or one, because the photos in the photo table do not always correspond to the brand table.

Other ing tables should be modified according to this method to meet our design requirements.

Overview after completion:

Download the sample database:

App_data200905280212