0

I am looking for a database design that can track and manage users with attributes to systems.

Situation: A user has access to many systems in the organization and within each system, different attributes such as date created, system login name, system user id, etc. Currently, we have a database that is a flat database so for every attribute is in one row and continues to grow as more system attributes are added. The current tool used is an Access database and the current column count is 231 columns (OMG)! This is getting out of hand.

Assumptions: There are a lot of attributes to track and one specific table in a database shouldn't be so broad to accommodate those attributes. Creating a table for each system becomes a mess in itself because each system may change in terms of columns. We need an interface for the administrators to easily manage this.

Let me give an example: User Sunny, who works at Google, has access to the DMV, IRS, and Whitehouse. For the DMV, Sunny has a user name, a pin code, and authorized IP address field. For the IRS, Sunny has a tax ID number, an organization number, a clearance number, and an access expiration date. For the Whitehouse, Sunny has a user name, a password, an access schedule, a portal address, and a user type. As Sunny works at google for years and slowly things change. Sunny gets access to the NRA, HP, Facebook, and Intel. The IRS adds a department clearance information. Whitehouse requires an authorization ID. The administrators need to see what access Sunny has and looks into the system; easily they find what they need.

Conclusion Modifying tables and adding attributes may change on a regular basis. Developing a single table to handle this seems like a chore. I need a database design that could accommodate changes like this.

Question How should design a database to accommodate something like this?

Thanks in Advance!

PS. I asked a question that is similar to this but it was a question for a software that actually exists to do this.

sunnysidedown916
  • 175
  • 1
  • 9
  • 1
    will you also need/want to change the user interface to manage any new structures - or is this overhead too much? – Randy Nov 21 '14 at 22:40
  • @Randy I would prefer not to change UI to update the structures. That would be too much work for the allotted resources that I have. Thanks! – sunnysidedown916 Nov 21 '14 at 22:45
  • 2
    then you will want some form of attribute pair structure. aka name-value pairs. these are tougher to work with but allow the flexibility you are describing and no UI changes. http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model – Randy Nov 21 '14 at 22:46
  • @Randy Thanks! Are there any other patterns that could be useful? You asked if the UI requires changing. If so, what pattern would you recommend? – sunnysidedown916 Nov 21 '14 at 23:16
  • 1
    Have a look [here](http://stackoverflow.com/questions/21010251/inventory-management-with-stock-options). That is a design trying to solve similar problem. – Mohsen Heydari Nov 24 '14 at 17:47
  • @MohsenHeydari Similar to the design to name value pairs. Thanks! This may help.! – sunnysidedown916 Nov 24 '14 at 21:53

0 Answers0