Using code tables

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Using code tables

Postby xtsr » Fri Jan 23, 2004 2:06 pm

I have a question concerning relations and whether my planned db-design agrees with Servoy...
Here's the setup of my tables (simplified):
- client (client_id, name,...)
- client_addition (client_id, add_id, val)
- code_addition (add_id, name, format)
As an example i can have the following entries:
- client
client_id = 1, name = 'Joe'
- client_add
client_id = 1, add_id = 1, val = '648-11-1234'
client_id = 1, add_id = 2, val = '12/06/1975'
- code_add
add_id = 1, name = 'Social Security Number', format = 'TEXT'
add_id = 2, name = 'Date of Birth', format = 'DATE'

In a tab on the form displaying client info i would like the following entry for Joe:
- Social Security Number: 648-11-1234
- Date of Birth: 12/06/1975'

This makes it easy to expand/adjust/customize the solution and can also be used for categories,... you just have to insert a new row into the code table.

While this denormalization is easy to handle with SQL, i'm not quite sure what the best approach would be in Servoy (as you can imagine, i'm new to Servoy). How flexible are relations? Is there a way to achive the resolution of the addition code with a lookup field?

Thanks for feedback,
Reto
xtsr
 
Posts: 101
Joined: Wed Jan 21, 2004 11:47 am

Postby Jan Aleman » Mon Jan 26, 2004 6:49 pm

The easiest to display your values in servoy is to create a listview in client_add, place a related field to code_add in it to display the columnname and put that listview on a tabpanel in the clients form.

To normalize or not is always the question.
If you normalize as far as you do (to a property based level) you have the maximum flexibility datamodel wise but coding will require much more effort, particularly for your search interfaces, reporting interfaces and calculation fields. Personally I don't normalize further than the point where each field has an identifying name and datatype unless there are very specific reasons to do so.
Any particular reasons why you want to normalize this far?
Last edited by Jan Aleman on Mon Jan 26, 2004 10:40 pm, edited 1 time in total.
Jan Aleman
Servoy
Jan Aleman
 
Posts: 2083
Joined: Wed Apr 23, 2003 9:49 pm
Location: Planet Earth

Postby xtsr » Mon Jan 26, 2004 10:35 pm

Thanks for your help! Real easy and works fine.

As for the normalization (oops, typo in the first post...), I guess I just designed in such a way, because that's the model i know best from my "regular" job in a very complex standard banking software/oracle environment, where a great degree of parametrization is essential. What I do in Servoy is on a very small scale compared to that and you're probably right in suggesting, that the higher flexibility might not be worth the additional programming effort...
xtsr
 
Posts: 101
Joined: Wed Jan 21, 2004 11:47 am


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 10 guests