We have Jobs, Employees, Employers and JobsEmployees. When somebody applies to a job we record in JobsEmployees the ID of the job and the ID of the employee.

JobsEmployees
--------------
JobID FK PK
EmployeeID FK PK

It is not necessary to record the employer ID in this table, but in order to prevent injection what would be a good practice here?

The problem will be that any employee may change the parameter passed in url for jobid, this way seeing the applicants for jobs not posted by them.

I am not sure if it makes more sense (a) to add a field EmployerID to JobsEmployees or (b) before the main query on JobsEmployees to add another query that will check if the jobid belongs to employerid; (c) use a join to link the Employer table and check if the jobid belongs to employerid.

I am tempted to use (a) as it seems to be the last costly operation. I am not sure of the consequences in the long term. For example, if I read the same table from the employer side, I do not need the EmployerID.

I don't think I see how changing the job ID would let you see other applicants. Wouldn't it just make you apply for a different job and not the one you want? I mist be missing something.

I'm not completely sure I see all of the consequences. If you go with (a), I think you would need to add the requirement that job IDs never change and are never reused. Otherwise it could be a problem if a second employer uses a previously used job ID. I don't think that requirement is unreasonable.

You could encrypt the IDs in the URL so users would have a hard time generating valid encrypted IDs. It probably wouldn't need to be a very complicated encryption scheme to prevent most users from messing with the IDs.

__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)

If a constraint at the database level prevent things like duplicate entries into a table, do I have to write the same logic at the application level?

For example, let's say an Employee try to add the same skill twice, the database will prevent this to happen. Depending on how we set the action it may throw an error or not. Now I want the user to be able to see that he already added that skill and I was wondering if

a) I will have to write the entire logic independent of database

like before the INPUT query I have to make a SELECT query to see if the skill is or not in the database (specific table) and only if it's not to run the INPUT, else to display a message.

b) I will have to write only a part and work with the database

like I make just the select query, if the database throw an error I catch it and display the message.

c) I do not have to write anything, simply set the constraint to "no action" and discharge the INPUT silently.

Until now I wrote everything as (a), but now I am wondering if with all the power the db gave me... maybe I can cut some corners or maybe writing all like (a) it is just a waste. Until this project, I didn't worked with constraints, so all the logic went at the programming language level. Basically the database has the same constraints that may be found in the code, so the constraints in database act like a safety mechanism. Maybe this is wrong and I can safely do (b) or (c) or other things that I didn't think before.

The database is the best place to ensure that problems don't occur. That way if you forget or make an error in the application code, the program still cannot corrupt the database.

Of course the program needs to be able to handle a database error. It can help by preventing errors in the first place. For example, by giving the user a list of choices or check boxes so they cannot enter an invalid choice. That's more work (and it should get its list from the database so you don't need to update the code if the database changes) but it makes for a better user experience.

When the form loads, I would have it query the database to see what options are possible and then make a CheckListBox or series of check boxes so the user cannot add the same skill twice.

Then use error handling just in case something goes wrong despite your best efforts.

That's the minimum you should do. Start with that and then add the fancier CheckListBox stuff if you can.

I wouldn't do that. The user will probably get mad that nothing seems to be happening but he doesn't know why.

I start with (b). Then add (a) if you can. Just be sure to get the information about what choices are possible from the database instead of hard coding that into the program. That way if you add or remove a choice, the program automatically adjusts so you don't need to change the code in both places.

__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)