Storing an array of type string[] inside a database

Question

I'm building a table of procedures, and I need to figure out a way so a 'logged in user' only has access to that procedure.

My initial thought is to store this information in a Certified column within the table like so 'JohnD', 'JasonD', 'JaneD'. Then I could only show that procedure if the user logged in is one of the usernames stored in the Certified column.

I gather users from an Active Directory, so I don't have a users table. I easily have access to their user name and full names etc.

The procedures table will have lots of procedures, then my KPI table could have many KPI's for each procedures. However I only want the person logged in to be able to view the KPI's he is certified for.

Am I going about this in a good way?

DwB · Accepted Answer · 2013-06-05 17:30:28Z

2

This is a many-to-many relationship. Try this:

Procedure table
  procedure_id
  ... other columns in the procedure table

User Table
  user_id
  ... other columns in the user table

User_to_Procedure table (this is the join table)
  procedure_id
  user_id

put one row in the user_to_procedure table per combination of user-can-access-procedure.

The user's table is optional if you have another way to identify the user (like active directory). In that case of active directory, the user_id in the join table is the identifier you get from active directory.

Here is a wikipedia link for join table. The wiki page refers to it as a junction table and lists join table as a synonym.

edited Jun 5, 2013 at 17:30

answered Jun 5, 2013 at 17:17

DwB

38.5k11 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Wilson Over a year ago

Is there a way to avoid having a users table? It seems tedious to have a users table when I have access to the active directory. Then if something in the active directory does change I have to make sure the users table reflects that change as well. I understand how this is the best normalized approach however.

Geeky Guy · Accepted Answer · 2013-06-05 17:17:11Z

0

Just because you're getting users from the AD doesn't mean you can't have a user table, or a reference to a user in your procedures table. You can populate its records on demand, that is, when a user needs to access something related to procedures, you can check whether that user is authenticated, and then you create the relevant record on the fly.

answered Jun 5, 2013 at 17:17

Geeky Guy

9,3895 gold badges46 silver badges65 bronze badges

Collectives™ on Stack Overflow

Storing an array of type string[] inside a database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related