Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
This is the forum to discuss the Wrox book Beginning Database Design Solutions by Rod Stephens; ISBN: 9780470385494
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 9th, 2012, 12:10 AM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default If we set a constraint in DB, do we still have to write the code for the same action?

I would like to ask what may sound like a silly question. We have a database well designed that prevents, for example, duplicate entries. If we set the FK to "No Action" it will prevent duplicate entries without throwing any error. If we set it to "Restrict" will throw an error. In the first case we do not need to write any kind of code in addition to what we have. In the second example we may write the code to catch this error. The third way, probably the proper way, it will be to write the actual code. But this is a costly action, we will have to query every time to check if the record is found or not in the database before actually insert it. I guess we may go away with any of those, but what constitutes the best practice? Thank you.
Reply With Quote
  #2 (permalink)  
Old May 9th, 2012, 09:47 AM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

I don't think I understand. Do you mean you want it to behave the way you describe: disallow duplicates if the field is No Action and throw an error if it is Restrict?

Why don't you just throw the error instead of writing code to set the field to Restrict? I think I don't understand.

A primary key or uniqueness constraint would prevent duplicates.

You might have to write a field-level check constraint (or trigger if the database doesn't support check constraints ;-) to watch for the value restrict.

Let me know if I'm misunderstanding.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
  #3 (permalink)  
Old May 9th, 2012, 12:40 PM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default

I am sorry I wrote everything in a hurry and I've not been clear enough. I have 4 tables:

Code:
Jobs
----
JobID
EmployerID

Employers
---------
EmployerID

Employees
----------
EmployeeID

JobEmployees
-------------
JobID
EmployeeID
Now let's say one employer is logged in, its UserID will be stored in the session (like $_SESSION['uid']).
Now if the employer want to see the employees that applied to a job he/she posted, will be able to see this by changing the jobID that will be passed as parameter.

If the employer change the parameter the select query that take the data from JobsEmployees will run with undesirable results, as we do not check the EmployerID that posted the job (Jobs.EmployerID). As result any Employer logged in will be able to see all jobs applicants for a job (even if the job posted do not belong to them) by changing the parameter in the URL (jobID).

I was thinking that I may prevent this in several ways and not sure what it will be the best practice:
To make a join between the JobsEmployees and Jobs and check the EmployerID against the session ID.
To add another field to the JobsEmployees table to record the EmployerID so the select query will account the EmployerID.
Or to make a new query that will check the EmployerID against the session ID, than moving to the query that select data from JobsEmployees.

Last edited by masterlayouts; May 9th, 2012 at 12:43 PM.
Reply With Quote
  #4 (permalink)  
Old May 10th, 2012, 04:06 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

Ah. I get it.

I think most sites would make the employer log in and then they would validate the employer's credentials to make sure they're not doing this. I'm not sure what the best way to do that. This is more a web site security issue than a database design issue.

But I think looking up the session ID would probably work.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

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


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I write out a record to an SQL db table? furjaw Visual Basic 2005 Basics 1 April 26th, 2006 05:16 PM
How to set Not Null constraint to Null Columns arasu Oracle 1 August 22nd, 2005 10:09 AM
Is It Possible to write record to DB Dwizz VB.NET 2002/2003 Basics 1 May 6th, 2005 07:16 AM
Write a script to select particular field from DB choudhmh Pro VB.NET 2002/2003 1 January 28th, 2005 07:37 AM
write result set bmains SQL Server 2000 11 March 9th, 2004 08:31 AM



All times are GMT -4. The time now is 04:02 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.