Storing multiple values efficiently

[MrAustin]

Hello everyone!

I am writing a database for my employer, and they would like this to store employee information. With Sarbanes Oxley out there now, they need to keep track of who has read access to programs, and who has write access to programs. My question is this: What method would you recommend to store this data in the database? There are about 10 different applications this applies to, so having 20 different Yes/No fields seems a little cumbersome, so I was thinking of storing multiple values in the field, then when the form is loaded, it will pull the values, seperate them, and show them accordingly on the form.

Any thoughts as to what is the correct direction to go would be great!

Thanks!

 

[FoFa]

I would have an Employee table, a Applications table and a Permissions table.
The permissions table would contain a FK pointing to the employee, a FK pointing to the application, and something to flag the type of permissions that employee has for the applications. This would give you what you are looking for. But read or write sounds rather simplistic, you could expand it to application role type (or something like that) or something like that if you needed to. Keeping the tables seperate give you that flexability. permissions could even be a seperate table you could expand on depending on your needs. Just a thought.

 

[MrAustin]

I would have an Employee table, a Applications table and a Permissions table.
The permissions table would contain a FK pointing to the employee, a FK pointing to the application, and something to flag the type of permissions that employee has for the applications. This would give you what you are looking for. But read or write sounds rather simplistic, you could expand it to application role type (or something like that) or something like that if you needed to. Keeping the tables seperate give you that flexability. permissions could even be a seperate table you could expand on depending on your needs. Just a thought.

For some reason I am having trouble visualizing this. I am new to database design, so to some it probably makes perfect sense, but to me I can't "see" it, ya know?

I'll take a look back when I'm less tired.

Thanks again!