2. PHPRunner General questions
  3. Force unique key values
This topic is locked

Force unique key values

 		1/6/2010 4:16:40 PM
PHPRunner General questions
phray author

A little info: We provide 'couples counseling'. Each counselor assigns each couple a 'couple number'. Each counselor also has a unique 'counselor id' number. When a counselor enters a record for that couple in the database, the record contains both the 'counselor id' and 'couple number'. when i setup the database i made an auto-incrementing field called 'id' that would serve as the primary key.
in phprunner i set the 'counselor id' and 'couple number' to be the key columns, thinking this would keep the users from entering duplicate data. but that doesn't seem to be the case. some counselors are entering the same couples multiple times, giving us duplicate information. i know they are entering the same info twice, because the records have different 'id' fields. this causes problems in the database because if the user tries to remove one of the duplicate records, it removes both occurrences because they both have the same data in the key columns.
the important thing here is that all counselors should have couple numbers that are unique to them, but the 'couple number' field can't be a primary key simply because each counselor will have their own "couple number 1"...
now I've thought of a couple of ways to keep the users from entering duplicate information, but don't really know which way would be better:

  1. use a 'before record added' event to check that there is no record in the database with both the 'counselor id' and 'couple number'.
  2. create a new field in the database that contains something like 'counselor id'.'couple number' and make it a primary key.


or is there an easier way that i'm simply not seeing?
S
 		steveh 1/6/2010


  1. use a 'before record added' event to check that there is no record in the database with both the 'counselor id' and 'couple number'.
  2. create a new field in the database that contains something like 'counselor id'.'couple number' and make it a primary key.
    or is there an easier way that i'm simply not seeing?


You need to do both.
A key to ensure that the data is consistent regardless of your programming.
And a before add and edit event to provide a proper meaningful error message (rather than just letting it fall into the error trap with a "primary key violation" message that most users will not understand).
Steve
T
 		thesofa 1/7/2010

Assuming you are using MYSQL, you can set up compound primary key, see here

scroll down and see how to set 2 fields together as a primary key.

you still need to do the error trapping to give the user a meaningful error message
phray author 1/7/2010



And a before add and edit event to provide a proper meaningful error message (rather than just letting it fall into the error trap with a "primary key violation" message that most users will not understand).

That's a good point, I'll have to get to work on that.



Assuming you are using MYSQL, you can set up compound primary key, see here

I am using MySQL and I had no clue about compound keys! That's very interesting and I think it will work perfectly.
Many thanks to both of you!
T
 		thesofa 1/7/2010

may i suggest that you have an ID field in each of the counsellor and couple tables, both autoincrement.

The third table is comprised of the same ID fields from the counsellor and the session, click on both to make compound primary key, a third field being the comments.

Voila, sorted except for the error trapping.

One of the effects of PHPRunner being so powerful and so well written is there are loads of people writing databases and front ends with quite thin knowledge of database management.

The temptation is to dive in and start making pages, but in reality one needs to spend a while thinking through the tables and normalising them.

It really pays off in the long run.

HTH

G
phray author 1/8/2010



may i suggest that you have an ID field in each of the counsellor and couple tables, both autoincrement.

The third table is comprised of the same ID fields from the counsellor and the session, click on both to make compound primary key, a third field being the comments.

Voila, sorted except for the error trapping.

One of the effects of PHPRunner being so powerful and so well written is there are loads of people writing databases and front ends with quite thin knowledge of database management.

The temptation is to dive in and start making pages, but in reality one needs to spend a while thinking through the tables and normalising them.

It really pays off in the long run.

HTH

G



Thanks for that, I've gained a new-found respect for DBAs since starting this project. However I am still new at this and definitely appreciate any suggestions. I do indeed have an autoincrementing ID field in both the Counselor and Couples tables, and will begin to make a third table for the compound key, but what sort of comments would go there?
I'm preparing to completely recreate this project with better choices for field names and types and such. I think I may need to pick up a book on normalizing databases. If I'm going to rebuild the database, I want to do it right.