Display Referance Data

ajinkyab277

Certified Lead Developer

over 8 years ago

Hi,
I have below scenarion , I am looking for some design practices for it

I have Employee list in Employee CDT

--------------------------------------

Employee
----------
empId
firstName
lastName
designationId

Designation
---------------
designationId
designationDescription

I want to show employee empId,firstName,designationDescription in grid.
Whats are the best possible solution to show designationDescription in grid with minimum number of database calls.

Below are some solution approach i have in my list
1. Create view for Employee Details
Cons:
1. Need to create extra view and CDT
2. Pass designationId list and get designationDescirption
Cons:
Desgination desciption might not come in sorted order of desgination id for in operator

Discussion posts and replies are publicly visible

Parents

0 chetany
A Score Level 1
over 8 years ago

Every approach has some tradeoffs.. A View can be used - the tradeoff is that, it reduces portability. You have to create the View manually. And it becomes problematic if you want to port the application to different databases - say from MySQL to SQl Server/Oracle - because the syntax is DB vendor specific. So, although Views can help - they reduce code portability - you will need to maintain multiple DB scripts.

Nested CDT can also be used. Note that Nested CDTs are not a totally bad idea. Nesting upto one level is just fine - and the Nested CDT models the Database model closely. Nesting more than 2 levels, will increase the query prepare and query execute time.

As @christineh mentions above, if you have nested CDT, the query built by queryEntity is same as the query that will get built for a DB View.

In case of nested CDT, you wont have to worry about code portability - since no View is involved. For simple entity relationships like this, nested CDT is fine.
That said- you can go with DB View at the cost of maintaining multiple DB scripts for each DB vendor.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 chetany
A Score Level 1
over 8 years ago

Every approach has some tradeoffs.. A View can be used - the tradeoff is that, it reduces portability. You have to create the View manually. And it becomes problematic if you want to port the application to different databases - say from MySQL to SQl Server/Oracle - because the syntax is DB vendor specific. So, although Views can help - they reduce code portability - you will need to maintain multiple DB scripts.

Nested CDT can also be used. Note that Nested CDTs are not a totally bad idea. Nesting upto one level is just fine - and the Nested CDT models the Database model closely. Nesting more than 2 levels, will increase the query prepare and query execute time.

As @christineh mentions above, if you have nested CDT, the query built by queryEntity is same as the query that will get built for a DB View.

In case of nested CDT, you wont have to worry about code portability - since no View is involved. For simple entity relationships like this, nested CDT is fine.
That said- you can go with DB View at the cost of maintaining multiple DB scripts for each DB vendor.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data