2010년 8월 2일 월요일 오후 6:33
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).
- 이동됨 Brian AurichMicrosoft Employee, Moderator 2010년 10월 2일 토요일 오후 5:26 migration (From:Windows Azure - Archive)
2010년 8월 2일 월요일 오후 10:09
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.
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,
- 답변으로 표시됨 sandip_ray63in 2010년 8월 3일 화요일 오후 1:50
2010년 8월 3일 화요일 오전 2:13중재자
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.