Specializations and Views

Classic Servoy
1

Hi,

I have a pattern in my db schema that appears numerous times, one such appearance is the data pertaining to ‘contacts’ in general.

Say there are four tables:
Table: contact
id serial8
name
date_created
importance etc etc…

Table: organisation
id serial8
contact_id int8 FK(contact.id)
[and a certain number of fields pertaining to organisations only such as incorporation_certificate etc]

Table: employee
id serial8
contact_id int8 FK(contact.id)
[and a certain number of fields pertaining to employees only… such as salary etc]

Table: individual
id serial8
contact_id int8 FK(contact.id)

… with sufficient keys etc to enforce a 1-1 relationship between *->contact where * are the aforementioned tables.

  1. Lumping all of that data into the contacts table is not an option due to current schema design.

  2. Using views on that design makes it a sinch to aggregate the general contact data with all the specialisations - employee, individual and organisation etc

But it appears that servoy doesn’t support views so I can’t simply create forms on each view individual_view, organisation_view etc etc.

What would be the best way to get around this problem?
Is it at all possible for servoy to work with views?
Custom SQL queries?
Some other servoy feature I don’t know about?
Some other postgres feature I don’t know about where a view can appear as a table?
Any other way?
Is that a bad database design?

Regards
Willie

2

According to: http://www.postgresql.org/docs/7.4/inte … views.html

I can use rules to create a table that is essentially a view but appears as a table… any thoughts?

3

willieseabrook:
According to: http://www.postgresql.org/docs/7.4/inte … views.html

I can use rules to create a table that is essentially a view but appears as a table… any thoughts?

Oops… read some more and this is not that case in reality.

4

Views are currently not supported other than via databaseManager.getDataSetByQuery(…), the main reason is that views in general are not updatable.

5

I found this:
http://www.postgresql.org/docs/7.4/inte … herit.html

I still have to give it a go and see how well it works in servoy (if at all) and also in hibernate as I also need a web side for some of the tables of the format I mentioned in my first post.