Best Practices for Numerous Lookup Tables in a Laravel App
I am looking for some best practices guidance on building a sophisticated records management app. The app is for recording incidents that a fire department responds to. The data is recorded by the firefighters who respond and is later sent to the state and then to the federal government.
The data consists of location information, response times, a narrative and dozens of various code sets. For instance, one code set is for the incident type. It’s a three digit code for which there are about 200 choices to choose from. There are many many more similar code sets that are much more in-depth such as a material first ignited or extent of smoke damage.
My question is… should I have a single table that lists all the codes from the various code sets and a “type” field that separates the codes in an SQL query or should there be one table, one controller, one model per code set. I tend to believe that the one table, one controller, one model for each code set makes the most sense as it would aid in addressing required rules. But that could make more than 100 controllers and models for just this one reporting tool.
I hope that makes sense. Any feedback will be greatly appreciated.
I would undoubtedly take the first approach of putting all codes into a single, normalized table with a type designation. You can still have multiple controllers for working with various result sets, but from a data management perspective it sounds like it can all be handled with a single model.
When it comes to retrieving the data from this model, you can filter by the necessary type or you could even setup query scopes to facilitate the interaction with that model.
Having hundreds of models for data that can otherwise be captured in a single table seems needlessly complex and much more difficult to maintain.
