Database design

Ahh my favorite at last!!! I was the sole incharge for the database for all the class projects I have done till date. So I feel very comfortable doing this. This project being a realtime scenario I was required to have more features with regards to security.

The main challenge was to understand the business problem. I was under the perception that the application accounts and server accounts are independent and there are no relation between them. It tuned that if this was taken as an assumption then admin would have to go through a lot of trouble to assign permission to the users. He would have to choose each and every user explicitly to provide them with acess to that account. Otherwise, users can be made into many small groups (user can be in more than one group) and assign groups to access that account. But this was rather clumsy approach. After two days of thinking with help of vinay we found a way of linking the server accounts, console accounts(accounts which will give access to actual application) and the application instance. Each application can have many instances such as development instance, testing instance, etc. so we considered application instance primary entity which is formed of the application name and the instance name. these instances have related accounts to them For example, suppose a siteminder application has a been installed on server1, it will be installed by a certain user. since serveral people must have access to this server account user will usually create a server account on the application name along with the application stage like If i am installing site minder for developers on server 2008 I will create an windows account siteminder_dev(username) and *** password. This password should be shared among all developer users. This account details are called ServerAccountsForApplication. To login the actual application installed on the server. Since it is a secured application I need to have a password and all developers have a user name and password (shared) same scenario as above. These application login details are called ConsoleAccountsForApplication. These both tables mentioned in bold are tied to aplication instance. This relation enabled me to give role based access to the user.