07-20-2023, 05:39 AM
I am working on a project that is a customized and specific CMS. In the frontend many of the fields will have pre-populated choices. However for these fields there needs to be an "Other" option that allows for a user entered text string. The project scope does not want these new "other" values to be added to the pre-populated lists (now or in the further). It is just an exception. The project scope is insistent on this flexibility which is implemented throughout the app.
I have database tables that contain all these pre-populated lists. I call these tables my list tables (all begin with "list_")
My question is about storing the choices a user makes. If it was not for this flexibility I would store the value as a foreign key to the appropriate list table. However, it makes sense for these fields to store the value(string) rather then the key. The drawbacks are indexing (minor), content control (minor), global updates i.e. changing a value in the list table will not retro-ripple through the system unless coded to (pretty big issue).
I will also mention that storing the data as values and not keys makes the programming and functions much simpler too (I am writing a service layer and it cuts down on joins and allows functions to be more generic).
Store as a value(string) not a key is the course the team is wanting to go.
Am I making a big mistake by doing this? Or is this fairly common? Are there other issues to consider?
Alternatives:
My alternative would be to add the "Other" string as a new row in the lists table and use a field to make it "hidden".
I have database tables that contain all these pre-populated lists. I call these tables my list tables (all begin with "list_")
My question is about storing the choices a user makes. If it was not for this flexibility I would store the value as a foreign key to the appropriate list table. However, it makes sense for these fields to store the value(string) rather then the key. The drawbacks are indexing (minor), content control (minor), global updates i.e. changing a value in the list table will not retro-ripple through the system unless coded to (pretty big issue).
I will also mention that storing the data as values and not keys makes the programming and functions much simpler too (I am writing a service layer and it cuts down on joins and allows functions to be more generic).
Store as a value(string) not a key is the course the team is wanting to go.
Am I making a big mistake by doing this? Or is this fairly common? Are there other issues to consider?
Alternatives:
My alternative would be to add the "Other" string as a new row in the lists table and use a field to make it "hidden".