Database structure for a list of items ( very different )

Question

I came to the following problem: I have a list of users that can create lists I have tables that contains entities like: - movies - with different properties - pictures - with different properties - people profiles - with different properties - X type - with different properties ( a to do list).

A user can create a list ( where other users can add elements) with all the above types. ( for example a list with movies and actors and pictures galleries).

How can I store that list efficiently and without many problems :)

Thanks in advance

Answer 1

If I understand your question correctly, you should create a separate table Entity that would be a base type for all your movies, pictures, etc. And a separate table ListsItems that would contain following columns: (list_id, entity_id), and table UsersLists of (user_id, list_id).

UsersLists would contain users to lists mapping (every user may have many lists), ListsItems would contain lists to entities mapping (every list may have many entities), and Entity would contain entity type (movie, picture, whatever) and specific entity ID that would point to its native table (Movie, Picture, etc).

Aufziehvogel asked you about number of types because it's important for this entity type field resolving in a design. If you have a finite predefined number of types, you may make column entity_type an Enumeration, but if user should be able to create their own types, that's a more complicated problem and a table SpecifiedEntity should replace specific tables (Movies, Pictures, etc).

You may read something about normalization of relational databases to understand this issue in all the existing details.

Answer 2

The solution you're looking for (and that Mkaz started describing) is a database pattern called Disjoint Subtypes.

I battled with the same problem a while ago, here's what I found:

Object-oriented-like structures in relational databases

Polymorphism in SQL database tables?