DB denormalization and UI lookups

[afrizen]

In my original MySQL DB design, table status is stored as an INT with a foreign key to an associated status table that contained the status options. I anticipated mapping the DB INT values to their UI equivalents for UI display purposes.

Now that I'm looking at the UI options in LC, it doesn't look like having a popup or dropdown menu display a list based on a DB lookup is easy or smart (correct me if you think otherwise). That leaves me with placing the actual status values in the popup/dropdown field.

So the question is would you store those strings in the original DB table as a VARCHAR? Not the best design pattern, but may make the best sense. Alternatively, you could map those strings to an INT that is then saved in the original table.

Feedback?

Thanks!

[afrizen]

I must have over-complicated this question. Let me try again:

Let's say you have a combo box. If you want to populate it with a set of optional values that are then saved in a DB, you have two options: 1) enter the values in the LC Menu Items field and then save them in the DB as strings or 2) look up those options from ANOTHER DB table that only contains those combo box options.

The later option allows you to not store the redundant strings in every record you save and, instead, store a simple integer to the DB.

Can the later option be accomplished in LC, and if so, has anyone had success with it? My research suggests the answer is 'no', and that everyone is simply storing the menu options redundantly throughout the DB. I can't see how one can populate the combo box with values from the DB based on a lookup.

Is this clearer?

[sturgis]

Don't see why you couldn't populate things using a db lookup. If my (simple) understanding of it is, you have an integer representing a state, but want to be able to choose a state textually that can then look up the matching state integer.

First, you can populate a list simply by putting a line delimited list into the button

put myLineDelimitedList into button "the name of your combo button" so that part is easy. Doesn't matter WHERE the data comes from, so it could be hard coded, stored in a property, the results of a query or whatever.

If your list of possible "states" will always remain unchanged there isn't much reason to create a db just for that list (if its oft changing then that is a different story) If you need to be able to a) populate a list into a button, then choose a specific entry for that button programmatically thats easy enough too. Say you set your list, and it matches (in order) the integers in your database, you can "set the menuhistory of button "buttonname" to whateverInteger.. In this case whateverInteger means the line number of the list thats stored in the button. If you don't have an easy 1 to 1 ratio, you can still look up whatever entry you need from your database, grab the matching name from your property or database or whatever and find the offset.

So if you have popped a list into your button and know that your state is 3 which matches "fred" you can use lineoffset() to figure out which line in the button matches.

Something like: set the menuhistory of button "mybutton" to lineoffset("fred",button "myButton") and voila' the line with "fred" is selected.

Putting stuff into a combo box is easy, choosing which item is selected in a combo box is easy, and yep. Having a lookup db table/property/array/whatever to do the cross matching should be pretty straightforward too. Shouldn't be necessary to store the redundant data in your main table, there are enough other ways to get it done.


Sorry for the kinda scatter shot answer, still wasn't completely sure where the issue was so answered everything I could think of.

[bangkok]

-how many "status" do you have ?
-and how many lines in the main table ?

->you used yourself the word "denormalization". Often, it doesn't make sense, from a practical point of view, to have too many tables in a DB scheme.
If you have a few "status", with only one label per line (no translation for instance), and you don't plan to change them, and that you don't have millions of lines in your main table... why bother using a relationship ?
That's the first point.

->If you really you want to keep it that way. Then you can do a SELECT on your status table (label and INT key), and put the whole result within your button.
That will give you :
StatusA, 1
StatusB, 2
StatusC, 3

With commas.

And then in the option button script, if user selects line 2 (StatusB), you'll be able to catch the INT by using "put item 2 of the selectedtext of me into intKey".

[walkerl26]

Here's another approach I recently used with great success:

Instead of storing an INT and using it as a foreign-key to the actual strings, make the status in the original table an ENUM instead. In your application, you can query the MySQL "information_schema" database and retrieve the list of ENUM values, then use those to populate your option-button.

This has the advantage that the strings are only stored in one, central place. The DBA updates the list of valid ENUM values in the database schema, and all the apps automagically reflect the changes the next time they run...

Querying the information_schema seemed pretty intimidating when I first thought to try this, but the tables have very descriptive names, and it turned out to be much easier than I'd feared.

Here's the guts of the code, which uses Andre Garzia's excellent DB Lib package to abstract the database calls:

-- get the column-type for this column
put empty into theArrayC
dbResetQuery
dbColumns "COLUMN_TYPE"
dbWhere "TABLE_SCHEMA", dbName
dbWhere "TABLE_NAME", tableName
dbWhere "COLUMN_NAME", fieldName -- convention: DB columns and LiveCode fields must have matching names
put dbGet("COLUMNS", schemaConn) into theArrayC

if theArrayC[1]["column_type"] contains "ENUM(" then
--we have an ENUM'd column: collect the enum list for this column
--note: for an ENUM'd column, we get back a string like: enum('Project','Principal','AP','Contracts')
put theArrayC[1]["column_type"] into theVals
-- and strip out the MySQL syntax wrapped around the list
replace "enum(" with empty in theVals
delete the last char of theVals
replace "," with return in theVals
replace "'" with empty in theVals

-- and finally populate the new option-button's values
set the text of button fieldName of group groupName to theVals
end if

I was very pleased with how nicely this works: makes an easy-to-use UI and makes the DBA/developers life much easier if (no, when) things change...

Larry Walker