Designing this DB has me stumped 2

[RhondaJC]

We're creating a database for our IS Dept that tracks PCUsers. The idea is, to give all Managers a Runtime that will allow them to request a User be added to our server, and also which Operating System (yep, we still have DOS users), software etc. Another area is Security. Our Accounting software requires permissions given by various owners within the software. We'd like the database to track requests, changes and approvals for security on that software, in addition to the requests previously stated.

So...I create the Users Table, a Software Table, but then I need to track severals yes/no options for permissions within the Accounting software. 8 different areas have 15 different yes/no questions. We want the Manager to have the ability to view the user's status at the various stages of approval.

That's where I'm stuck. The user can have permissions in more than one area of the Accounting Software. Each of the 8 areas 15 questions are unique.

I'd like to read your take on how you'd design this database. Every option I've come up with, ends up duplicating information.

Thanks in advance,
Rhonda

 

[grnzbra]

8 x 15 gives 120 DIFFERENT questions or could some of the areas have some questions that other area also have?

 

[RhondaJC]

Exactly! 120 unique questions. See my problem?

Rhonda

 

[RhondaJC]

I'd like to clarify, that I am not asking for free Professional help in designing this database. I don't want anyone to feel that they are giving away the farm, so to speak. I have tried giving each security area of the software, it's own table, but that turned into a huge relationship mess. I'm wondering if it would be better to use one table for the questions, and another for the answers. The Answers table would use the QuestionsID, an AnswerID, and a Text Description field. A third table would be for Status, ie StatusID, Approve, Request, Change, Delete. A usersdetail table would have records that use the IDs from each table, along with StatusID. Am I on the right track?
Rhonda

 

[markphsd]

This wouldn't provide a Yes No Answer Scheme, however I think its more adaptable. Yes/No is so limiting.

Access to Software:
A Users form a with a subform in data sheet or continous form view for selecting access to various Software.
UserID, SoftwareID. That simple.

The Trickier One.....

Access to Accounting Areas:
For your accounting table I would suggest.
AccountingAccessID, AccountingAccessAreasID, AccountingAccessQuestionsID

or AAAccessID, AAAreasID, AAQuestionsID, UserID

Then, for each user, you have the db automatically insert all the AAreas's avalible- there's yours Areas.

You'll need a lookup table for the Area's then of course.
AAAreasID and AAAreas

You'll also need a lookup table for the Questions. In that table you'll have to have three fields, AAQuestionsID, AAT and AAQuestions

Have in your accounting form a filter, like a unbound listbox that will filter out the area you select from it.
Make sure that form is continous or datasheet, that will make it easier to use. Have a listbox that selects from the AAQuestions, make sure that you filter the questions based on what the Area is.

If that's not clear, I'll send you my idea. I have lots of time on my hands.

 

[JeremyNYC]

Rhonda,

You're on the right track, but I would actually normalize this one step further, as I PROMISE you that one of your groups will decide to use a sixteenth question, and that would just lead to you adding more fields to a few tables and that would get too difficult to maintain.

You'll have tblUser as you would normally, though I ALWAYS call this tblPerson, because what happens when you want to store a manager? You don't want to make a new table for that, I promise.

Next you'll have tblQuestionSet, which will have a QuestionSetID and a QuestionSetName.

Next you'll have tblQuestion, which will store QuestionID, QuestionSetID, QuestionOrder, and QuestionText.

Next you'll have tblAnswer, which could just store QuestionID and Answer, though you might want to denormalize and add QuestionSetID, just to make some of your queries a little faster. Also, you might want to use a numeric field so that you can store integer values other than 0 and -1, giving yourself a little more flexibility. But this would require another field in tblQuestion and another table for the answer key for those questions that have more than two possible answers.

There will, of course, be other tables, but it looked like this was the area giving you a hard time, so I thought I'd address this bit.

Hope this helps more than it hinders.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[RhondaJC]

I agree that the Yes/No Answer Scheme can be limiting. I've found that it's very difficult to run any kind of evaluation reports without jumping through hoops when using yes/no.

The answers are very helpful. I appreciate the time you both have taken for me.

Alas, the 'Powers that Be' have given me a new "Critical" project, so I won't be able to work on this for a while.

Jeremy, I'm not a Professional Database Designer, but I have read everything I can on Normalization. Thanks for the insight. I try to leave a database that can be understood by my replacement in case that famous bus runs me down.



Rhonda