I'm working on a project at the moment that has a rather unusual requirement and I'm hoping to get some advice on the best way to handle it or even some pointers to info that can help me build a solution. Ok, so this is what I need to do. The application stores and manages various types of media files but each deployment of the application has completely different metadata requirements for the media files. This metadata can contain an arbitrary number of fields of different types (single line text, multi-line text, checkboxes, selected values, etc.) and also often requires validation particularly presence and uniqueness validations. The application needs to be able to easily retrieve values and most importantly has to be able to handle full searching capabilities on these fields.
I'd use something like elasticsearch to handle the searching side of this, rather than having some monstrous join to join your metadata key/value table. With this you store your data as you like it - as long as you can produce a json document representing the record with all these metadata fields that should be searchable it won't care how your relational database stored them. You might also want to try a document oriented database like mongo rather than forcing a relational model on this (you can actually use elasticsearch like this too)
Fred
