To Norm, or not to Norm...?

Classic Servoy
1

I have a number of value lists that I want to store in the SQL backend. These are distinct groups of values used in different areas of a large solution. The user will manage these values via the solution preferences.

For example:
Contact types
Project types
Master tasks
etc.

So, my question is, should I put each value list into its own table, or should I be combining them into one table called “valueLists” (how we did it in the old days with that “other” platform)?

Keep in mind performance is essential, as many tables in the solution will have 100,000’s of records, and there could be 100s of users on the system at one time.

I would appreciate an opinion on this.

2

I’ve decided to put everything in separate tables. Offers me more flexibility down the road.

3

Gary,

I am in the midst of engineering for some SAAS things with high row counts as well … amidst learning the product and a number of experiments on this end :)

Our engineering is using the following technique (not yet load validated but I am comfortable with this methodology with previous J2EE initiatives that had high volumes on the web in a Sun/Oracle cluster):

  1. Parent table object to describe the valuelist or conceptually related item that needs persistence;

  2. The child table uses a regular 4 byte INT (not BIGINT) for its PK. Other higher row count tables are using BIGINT’s as the PK. The child table acts essentially as a name-value pair (which form the “value list” items). The valuelist can then use either SQL providers (via routine Servoy relations and data providers) or issue a direct SQL call. Optionally, per an earlier thread or something in Servoy Mag I read (somewhere, sometime :) ) one could issue a pre-load of the commonly used valuelists at solution/form load to avoid repeated calls to the db engine for each valuelist call. The INT from the child table is stored in the referencing table as a Foreign Key back to the child table.

Works fine for us in tests so far. No doubt there is more than one good solution to this!

Michael

4

I’d go for 1 set of tables: valuelist, valuelistvalues & valuelistvaluetranslations (offcourse you can use differetn names :-) ), with support for system and customer defined values, and active/inactive items.

A midsize to large solution will end up with quite a few valuelist. If you create a table for each one of those, you’ll have a lot of tables in your database, which in itself is not a problem, but it’s just not needed.

Paul

5

Paul,

Sure … good tip and fair enough … thank you …

I pass along the valuelist name down to the child tbl as well (so the Filter in the Valuelist screen has something to work with). I ended up using a partially normalized model since there are other aspects needing control (at our site anyway) with a parent table.

Thx,

Michael

6

Paul, just one question.

I’ve been trying to define a normalized model like yours to store valuelists, but I’m not able to figure out how to make it multilingual.

In a model like yours, I guess that you have to load all valuelists on solution open by using a SQL query with current language as a filter, is that correct?

7

Yes, you are in control to load the valuelists