Hi,
I have a question:
When you have some multiple dropdowns (with items stored in reference table), do you always store your data in 2 different tables ? a first table for the data source, and another table to save the multiple values and make the link with the first table.
I have already seen, multiple values stored in a single table with concatened values to store the keys in a text field, but unfortunately, this seems not a good way because of the DB foreign key constraints missing.
Regards
Discussion posts and replies are publicly visible
Storing a string of relational values is a potential "quick and easy" solution when one is required. I recommend that instead of reinventing the wheel, anyone who does it this way should instead cast their array of IDs as a JSON string, then write the JSON to the database. Of course there are still things to worry about such as whether any entries might plausibly grow beyond the column text size you define, as well as the lack of foreign keys which you already mentioned. Overall I think the strongest, most scalable and most error proof method is to use the 2-table approach - but this all depends on your own requirements and the target audience.
Thanks a lot for your reply. Very interesting the json aspect, I will take a look at this way to do.
What I have trouble understanding, is why the lack of foreign key is not the first criteria to take in consideration.
Appian works with relational databases so I'm a little surprised.
If the reference table items need to be updated (to deactivate some rows for example), it will be very diffciult to update the json field or to query the corresponding key data.
cedric01 said:is why the lack of foreign key is not the first criteria to take in consideration.
For many this might be a valid first consideration. However I don't believe FKs are absolutely required as long as your data is well-managed from the front end; you can, for example, simply store related table IDs in a plain integer column with no FK added, and as far as Appian is concerned there'll be basically no difference (aside from DB optimization / indexing issues).
Ok I see your point of view.
Thank you Mike.