Join Table vs Reference Field List?

Ask for Help
1

I’m curious of the benefits of using a Reference Field List vs a classic normalized Join table to represent many-to-many relationships? In your class enrollment example, you have a classic join table with the Enrollments (each student can take many classes, and each class can have many students). Presumably, you could also have represented this with a Reference Field List to classes in the Students table. So why choose Reference List vs a Join Table? I assume the Reference List just stores the data as a JSON object in the database, so the searching is limited? So a Join Table would be more powerful? Thanks

2
  1. The Enrollments table has additional columns (Notes, Status) that can contain extra data for each enrollment. You can’t do that nicely with a Reference List.
  2. Reference Lists are somewhat new, I think the template existed before then.
  3. References are stored as row IDs (numbers) and Reference Lists are stored as JSON arrays of rows IDs. So your guess about JSON is correct, but it doesn’t affect searching. Searching actually happens in the frontend, not the database, and in any case both kinds of references still have to deal with row IDs as a first step.
1 Like