Restricting Viewable Data

I currently have 2 tables: 1 for users and 1 for jobs. I would like to have a user log in and view only the jobs he or she works/worked with. A job can have several users working with it and a user can work with multiple jobs.

My proposed solution was to add a new database for each user (called Access+username). When a user logs in, that user's access db would be queried and only those jobs would be shown to the user.

Is this an adequate solution or is there an easier way to get the restriction I am looking for? Thanks in advance.

You want users to interact with the database directly??
This is strong discouraged!

I'd recommend writing a program that accesses the database for the user.
You have much more control that way.

No. I want to build a web app that does this. The end result would be a user logging in, and the app displays a list of all that user's jobs. I want to know the most effective way to get the system to show just one user's jobs rather than every job in the system.

Have you done work already?

How are your tables structured?

Some questions you might want to ask yourself are:

• Can an employee work on more than one job?
• Can one job have more than one employee assigned to it?

This is crucial information. If a job will only ever have one employee, you could put a field in the job table relating to the employee table (this would be a foreign key). If an employee will only *ever* have one job, you could put a field in the employee table representing the job they were assigned.

However, most likely, an employee will have many jobs, and one job may have many employees... This is called a Many-To-Many relationship.

The easiest way to deal with many-to-many relationships is to use what's called a bridging table. This table will contain keys joining employees and jobs.

I won't go into detail, in fear that you already know this stuff... but in case you didn't, maybe this will get you started. Feel free to ask questions...