Storing an array of type string inside a database
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
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?
2 Solutions collect form web for “Storing an array of type string inside a database”
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.
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.