I'm working in a project (with Python, SQL, Javascript) where the devlopers before me have started saving sets into database fields.


enter image description here

Here the field "audience" can have sets as content. Is this really a good and working idea? I'm wondering because I was taught to never save lists into a field in a database and I was taught to use connection tables instead of lists / sets.

  • 3
    Working idea ? yes. Standard ? No, standard are using another table with foreign key and unique constraint (for set only not list), Good ? It depends of the requirments. If you really want to know if it's a good idea, we would need more information : volume of data, what kind of request are performed against the set ? – Walfrat Nov 6 '17 at 12:56
  • 1
    You don't have to save the list into a single field. Your example is just a display; you can put the audience in its own table and still have it displayed as a comma delimited list. – Robert Harvey Nov 6 '17 at 18:55

This is not a reliable and safe approach. Here some reasons why:

  • the same set element could be entered several time in the same field,
  • it's difficult to verify that each element in the set is valid,
  • you're limited by the maximum value length of the field
  • finding rows with a given element is slower, because you need to use a wildcard search, preventing use of indexes and forcing the DBMS to go through all the rows
  • if items have variable length searching for item XXX with wildcards might also catch false positive such as item XXXYYY
  • order of items in the field is not controlled, so it's difficult to find rows having several specific set elements (because if looking for a row having 3 elements they could be stored in the order A,B,C or C,B,A or B,C,A or C,A,B or B,A,C or B,C,A or A,C,B ro either requiring 6 queries with wildcards around the elements searched, or 3 independent queries and then looking for the rows that appear in the three queries)
  • it's very difficult to make more complex queries using the set elements (you can but it's a lot of hand crafted code and unoptimized access).

On the other side, it's an easy, quick and dirty approach, that could be acceptable if the set elements are purely descriptive and are only of marginal use.

The relational way of doing this would be to have a distinct table to represent the set, so for one record having a specific ID as primary key, you would put in the set table, for each of the relevant set elements a new row with the same specific ID and the set element.

Not the answer you're looking for? Browse other questions tagged or ask your own question.