Search code examples
databasedatabase-designrelational-databasedatabase-schemadatabase-normalization

Database design users-categories-pictures (cyclical relationship?!)

I have to design a simple database, but things are harder than they appear. It's very simple to fall in a cycle relationship.

  • A user can choose one or more categories (e.g. animals, humans, landscape, nature, abstract etc.)
  • The same category can be chosen by other users (n-n relationship between users and categories)
  • Pictures can be assigned to one or more categories so a category can have one or more pictures (n-n relationship between pictures and categories)
  • Now it's time to historicize viewed pictures so I think there's another relation between pictures and users, which is also n-n, because a user could view one or more pictures and the same picture could be viewed by n users

Users <---> categories <---> pictures

Users <---> pictures

In my opinion that design has a cycle relationship, could you help me solve this situation? Have you an idea?

enter image description here

Solution

  • I don't see a problem here.

    If all of the relationships were in use for a query, it would probably be of the type "Show me the pictures in this user's categories that they have not yet viewed", which seems like a reasonable business case.

    So you'd have a query that joins users to categories to picture, and checks for non-existence of a record in the "user_picutres_viewed" table for that picture and user.

    Not a problem.