Design alternatives for listable sets in a database

Question

I'm creating a feature to allow lots of different types of things to be added to lists. A list has some basic elements like a name and description and owner id.

So my first data model is

 List:
  list_id
  list_name
  list_description
  list_owner_id

And my second data model looks something like:

List Items:
 list_item_id
 list_id
 rank/order

I'm trying to decide some basic things:

should I:

1. make a generic list table that specifies the types of items that it's list elements point at, i.e. (List: element_type) or

2. make separate list tables for each type of list or i.e. (Product_List, Product_List_Items , Comment_List, Comment_List_Items)

3. make the list elements point at a generic "listable" element that then finalizes/specifies the type of the thing pointed at for final lookup. i.e List_Items: element_type

4. or some other thing

If I do option 1, I can select a list from the list table, then choose to do joins based on knowing the final element table to join against

If I choose 2, I will always have static relationships that are well defined, with only specific data in each table

If I choose 3, I will be able to store a variety of things in each list, but this is not a requirement at this time.

Update: my question is similar to this:

DB design to use sub-type or not?

but instead of a one to one relationship I have a one to many...

Answer 1

I usually recommend the design that is your option 3. Just like you would create a superclass (or interface) in an OO programming language, and each of your subtypes "IS-A" instance of the superclass. You can do something similar in SQL, but the IS-A relationship is handled through referential integrity.

So your List_Items table references Listables, which is a parent table for all the types of entities that can become part of a list.

I've given this answer numerous times on SO, usually for questions taggegd polymorphic-associations. And I talk about it in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

I don't recommend the solution described by @Gilbert Le Blanc, which is a form of the Inner-Platform Effect antipattern. SQL already supports data types, so you should use them instead of creating a synthetic data-typing system layered on top of SQL.

Answer 2

If you're going to have a limited number of lists, then your option 2 is easy to understand, and you can use the correct database data types for each of the list element types.

If your lists are more numerous, then your option 1 seems like the best bet. You'd have a List table and a List Items table. Your list element in the List Items table would have to be a generic VARCHAR, You'd also want to store the format of the list element (INTEGER, FLOAT, DATETIME, etc.) so that you'd know how to convert the VARCHAR into the correct format.