Missing feature in the view editor

General Questions/Discussions
1

Hello,

yesterday I encountered what I consider an unnecessary and severe limtation in VP. I am currently running V13.1, build 20160606 - which is the current build, if I’m not mistaken.

I am currently using the ERD Designer for data modeling. For the sake of simplicity, let’s say I have 2 tables (T1, T2) which are connected by not one, but 2 one-to-one relations, as follows:

T1.F <—1:1—> T2.F1
T1.F <—1:1—> T2.F2

Modeling this with the ERD Designer is not an issue. The issue arises when trying to build a view reflecting that situation with the view editor, due to the following restrictions:

  1. It does not seem to be possible to use more than one instance of an entity in the view editor
  2. The view editor cannot be used to define aliases for entities, only for columns
  3. Also, the view editor does not allow you to use a view as an entity

Admittedly, I am not an expert VP user yet, thus I might be missing some features.

So here’s my question: Am I indeed missing something, or is this really a missing feature?

Thanks in advance for any help/suggestions.

2

I’m sorry that the issues you experienced are the current limitation in our view editor. I’ve pass your case to our engineers to evaluate the possibility to support this, and I’ll keep you post once there are any feedback from our engineers. Feel free to contact me for any questions and wish you have a good day!

Best regards,
Rain Wong

3

[quote=Rain]I’m sorry that the issues you experienced are the current limitation in our view editor. I’ve pass your case to our engineers to evaluate the possibility to support this, and I’ll keep you post once there are any feedback from our engineers. Feel free to contact me for any questions and wish you have a good day!

Best regards,
Rain Wong[/quote]

Dear Rain,

thank you for your reply.

I have looked a bit more into the matter, and it really seems that the view editor supports neither entity aliases nor multiple occurrences of the same entity in a given view. And that is indeed a severe limitation, basically making the view editor useless and crippling the whole product’s usefulness for serious data modeling.

To illustrate how severe the issue is, here’s a very simple example: Let’s assume we want to store data about persons and their addresses. Thus, we have 2 tables


Person(ID, FirstName, LastName, DateOfBirth, HomeAddressID)
Address(ID, ZIP, City, Street, Number)

with one obvious relation


Person.HomeAddressID >--- 0,n : 1 ---> Address.ID

Obviously, a corresponding view would be


SELECT
	Person.FirstName, 
	Person.LastName, 
	Person.DateOfBirth, 
	Address.ZIP, 
	Address.City, 
	Address.Street, 
	Address.Number 
FROM Person 
INNER JOIN Address
ON Person.HomeAddressID = Address.ID

So far, this view can be built using VP’s view editor.

However, once you want to introduce business addresses, your data model changes:


Person(ID, FirstName, LastName, DateOfBirth, HomeAddressID, BusinessAddressID)
Address(ID, ZIP, City, Street, Number)

and


Person.HomeAddressID >--- 0,n : 1 ---> Address.ID
Person.BusinessAddressID >--- 0,n : 1 ---> Address.ID

The view showing persons and both their home and business addresses needs entity and column aliases and 2 inner joins, using the Address table twice:


SELECT
	P.FirstName, 
	P.LastName, 
	P.DateOfBirth, 
	HA.ZIP AS HomeZIP, 		
	HA.City AS HomeCity, 
	HA.Street AS HomeStreet, 
	HA.Number AS HomeNumber, 
	BA.ZIP AS BusinessZIP, 		
	BA.City AS BusinessCity, 
	BA.Street AS BusinessStreet, 
	BA.Number AS BusinessNumber 
FROM Person P 
INNER JOIN Address HA
ON P.HomeAddressID = HA.ID
INNER JOIN Address BA
ON P.BusinessAddressID = BA.ID

And this view cannot be visually built with VP’s view editor.

As far as I can see, it is possible to manually edit the SQL for such a view in VP, but at that point a good text editor does a better job, rendering VP almost useless from that point on.

4

According to our engineers we will support showing the same entity multiple times in the view editor also create view from columns in other view in our future release. I’ll keep you post on new update about this enhancement. Feel free to contact me for any help and wish you have a good day!

Best regards,
Rain Wong