Hi,
I have decided that I want to store valuelist values in a table.
As I may have many lists with the values defined by the end user (admin) would “Good Design” be to have a table per list, each with its own edit/update form.
or
A single table for all lists with a single edit/update form filtering out depending on the current form?
Pros & Cons…
Thanks
I go for a middle course - one table for all ValueLists but multiple Forms to allow Admin to add/edit data. List of Value Lists are filtered using VL_name field.
Pros. Reduces number of tables. Keeps data & methods for VL’s in one place
Cons. Some redundant fields since you need a field for each different ValuelList component
HTH
Regards
Graham Greensall
Worxinfo Ltd
It’s kind of difficult to say one way is the right way in all situations. But this is my general rule of thumb.
For important and/or lengthy value lists (generally they go together), we always use a separate table to define the values. This way the relationship is also tied into the database itself with a primary key/foreign key relationship. The advantage here is that data integrity is maintained regardless of how the database is accessed, via Servoy, some other application or through direct queries. It also makes the database/solution more portable. We also always use a separate table if the valuelist can change over time and we want the knowledgeable user (our datamanagers) to be able to change the valid, allowed values that are in their domain over time.
For smaller, ‘less important’ valuelists I tend to only define them within Servoy itself. We have a rather different type of database than most of the users of Servoy in that it is not a ‘business’ solution but rather a frontend to a research database. So important valuelists like diagnosis or site of disease or protocol will always be in separate, defined tables and valuelists are defined through those tables within Servoy. The other thing is that with a separate table for each valuelist one can also have other columns defined in that table - beyond the simple id/code and description - that are unique to that particular relationship. To go to the other extreme, though, for something like ‘sex’ that has a very limited set of values we always use just ‘internal Servoy’ valuelists to define valid entries.
That is one perspective. As I said I think it is difficult to come up with any hard and fast rules or even best practices that cover all situations though and I’m sure others have different criteria.
The other type of value list that i am using is to simply nominate the values from the same field and using type ahead to get the list. if the value is not in the type ahead list the users enter it themselves and it will become available from then on, simple.
The only issue i can see is that the values in the list will always be there, whether they have become redundant or not with time.
Rodney, Could you add a date field lastAccessed and display a VL based only on records that were accessed in a given time period?
For that i think you would have to create a table unique for that value list and then you could additional fields (ie last accessed) and grab the values through a relationship.
Similar to filemaker “show only related records” type ahead.