Database Foreign Relations

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".

---

In general, if you can store the foreign key rather than the value, the enforced consistency that it buys you will typically make things simpler in the future.

However, the "requirements" make it sound as if that is not an option, which makes it "feel" as if there is an inconsistency in the data. It sounds like the goal is two store two types of data in one field: either a pre-defined value or a user selected value and the two values have different meanings. Without knowing more about the system, it's hard to say for sure, but that could make certain types of queries a little more difficult to write (but not impossible).

If the value is truly just a text value and the pre-populated lists provide an easy way to make choices, then storing the text value seems okay. However, the question makes it sound as if meaning is being attached to the values in the lookup tables. If so, then storing the foreign key may result in a more robust solution. But it may require that an "Other" value (with its associated foreign key value) be added to the lookup table and another free-form text field be added to the table to store the actual text if "Other" is chosen.

---

First, the concept of a "list" table in the way you describe feels nebulous. It would seem to indicate that the specification on those entities is not fully understood or vetted. You shouldn't need to differentiate "list" entities from non-"list" entities.

Second, it sounds like the problem you are having is when the user is given the choice to enter their own value as opposed to a item that might appear in a droplist. In that scenario, I would have a special foreign key value that represents "Custom" where I would then show a textbox for the user to enter their custom value. I would store the custom value in a separate column from the FK value. Whenever the user chose a non-custom choice, I would null out the custom entry (ideally that would be enforced via a Check constraint but MySQL doesn't honor them so you'd have to use a trigger). In this way, your CMS can simply look first at whether there is a custom value and then at the value from the FK. The advantage to having both the FK and a column for custom text is that you can easily change the makeup of the parent table (add attributes, add values, adjust values etc.) without having to affect the child table.