Data Retrieval query

Question

Hi All,

       We have a ASP.NET web application in which we are using Oracle 10g as the DB.Now in the DB there are tables Employee and Role and a association table Employee_Role.An employee can have 1-5 roles(USER,ADMIN,APPROVER etc).In the UI we have a grid which needs to show primary employee info like firstname,lastname etc and fixed 5 roles - all of these needs to be shown as columns(so that only one record will be shown for an employee - the role columns will be Y or N depending on whether the employee is having that role or not).Around 100000 employees are there and currently we are working on the Oracle query to return such result set in the format mentioned.This format also facilitates paging.

The format will be like

EmployeeID   USER   ADMIN   APPROVER   READ_ONLY   APPROVER_LVL

         1           Y            Y            Y                      Y                     100

         2           Y            N            Y                      N                     200    

 

instead of -

 

EmployeeID                          Role_Name                       Attribute_Value

 

         1                                       USER                                NULL

         1                                      ADMIN                                NULL

         1                                   APPROVER                             NULL

         1                                  READ_ONLY                            NULL

         1                                APPROVER_LVL                         100

         2                                       USER                                 NULL

         2                                    APPROVER                            NULL

         2                                APPROVER_LVL                         200 

     My question is what design approach should I take to have a areally good performance in such a scenario if I have to use Windows Azure Platform(I would like to use the Azure tables).

Regards,

Sandip

Answer 1

Hi Sandip,

 

I am not sure on the complexity of the application but here is what I feel with some assumptions.

The Data will grow over a period of time and so the need to scale this table is essential

The Roles will grown over a period of time

The employee information will be like a master data and not much of "Update" operation is anticipated on this table

 

The only way to have index in Azure Storage is basing your query on "Partition key" + "Rowkey", so my first suggestion is find a field to bucket your data into smaller chunk, example partition key could contain a alphabet A-Z and store the name in the "Rowkey" / define a unique id and put it into the rowkey so that way indexing will be taken care off.

To store the role information, As Azure Table storage schema is fluid type you can store the role information within the same entity row and while fetching/storing the data load the data into a dynamic class where each role will become a property. Note you could choose to have the role information in a separate table and decide upon a similar partition key + "Rowkey" fields appropriately. Also one should handle exception with field contains check while loading the entity->fields into the dynamic class.

 

example;

 

Partition key	Rowkey	User	Admin	Approver
A	Albert Nelson	Yes	Yes
A	Arun	Yes	Yes
B	Babu	Yes		Yes
B	Balu	Yes	Yes	Yes

 

Thanks & Regards,

Pramod.

Answer 2

Hi,

As Pramod said, you could store the "Employee in role" info just in employee entity. Because Azure table doesn't define table schema, we could add/remove/update the table entities' property dynamicly.

Thanks,

---

Mog Liang