Search code examples
sql-serverdatabasedatabase-normalization

Normalising image album database

First version

Above is my normalised database structure for my app. I am going to store Users, and their favorited Images. Images might be alone (hosted on reddit) or in albums (hosted on imgur), and they always have a title.

Question is - is the database set up correctly? I have this feeling that i have something wrong with ImageAlbum and Image table relationship.

EDIT: This might work? enter image description here

Solution

  • The main issue with the original design is that the intended relationship between a user and an image would not be possible, as the two tables are not connected.

    As a general rule of thumb, if there's a 1-1 or a 1-many relationship between tables, you can rely on constraints. I.e. 1 customer can place many orders. You have a Customer table with a CustomerID PK column, and an Order table containing an OrderID PK column, and a Foreign Key constraint to the CustomerID column of the Customer table. That establishes the relationship, and ensures that you cannot place an order if you are not a customer.

    An order typically consists of one or more products, and a product typically can be purchases in multiple orders. In cases like this, you cannot set up this relationship the same way. A common workaround for that is to do so using an intermediate table that establishes the many-to-many relationship.

    So building on the earlier tables, we also have a Product table, with a ProductID column as a PK. To set up the relationship between Order and Product, you would then credit an OrderProduct table, with FKs pointing to the OrderID and ProductID in question (and probably also something indicating quantity of products for this particular order, and perhaps something like a FK to a Discount or campaign table, and whatnot).

    So in your scenario, I would establish the relationship between Image and User using a similar approach, and simply adding a UserImage table to allow for the many-to-many relationship. You then also add an AlbumImage table to determine the many-to-many relationship between images and albums.

    As indicated in the comments, there's no need to have an AlbumTitle table, really. It would naturally belong to the Album table. The ImageTitle would belong in the UserImage table, because every user can add their own title to an image.