[Frunkie]

Cheers everyone,

I have two questions that I would like to find the answers to. I have a db where I am using unique indexes and will not allow dupes in the db. I am creating my insert forms and it dawns on me that if I have say for example 3 tables A, B, and C; A being the independant table and B and C are dependant tables, I am writing app code to first insert the data into A then B and then C to satisfy the constraints on the tables. If table B already has a record that matches what the user is trying to enter, what happens (or what should happen) to the data in the other two insert statements? Should the entire insert fail for all 3 tables or shoud the database take what data it sees at not being duplicated?

With regard to errors, how should I handle these? Should I output an error to the browser letting the user know that dupe data exists or should I program yet another page so the user can first look up the data to see if it exists?

My last question deals with tables that should be managed by a supervisor (I think). I have a table that deals with employee positions or titles whatever you want to call it. This table will be used for reports so the average user should not be allowed to enter a new value. If there needs to be a new value entered into that table, should I program another page that only supervisors have access to, that will allow them to add a new field?? How is this normally handled?

Thanks!

Frank

[r937]

Originally Posted by fjm1967

Should the entire insert fail for all 3 tables or shoud the database take what data it sees at not being duplicated?

this is entirely up to your business rules how you want it done

Originally Posted by fjm1967

With regard to errors, how should I handle these? Should I output an error to the browser letting the user know that dupe data exists or should I program yet another page so the user can first look up the data to see if it exists?

definitely the former

Originally Posted by fjm1967

My last question deals with tables that should be managed by a supervisor (I think). I have a table that deals with employee positions or titles whatever you want to call it. This table will be used for reports so the average user should not be allowed to enter a new value. If there needs to be a new value entered into that table, should I program another page that only supervisors have access to, that will allow them to add a new field?? How is this normally handled?

normally by SELECT permissions different from INSERT/UPDATE/DELETE permissions

[Frunkie]

Thanks for the reply Rudy..

Originally Posted by r937

normally by SELECT permissions different from INSERT/UPDATE/DELETE permissions

Is what you are describing here what are known as "Roles"? Where do these permissions come from?

Thanks..

[r937]

through the GRANT statement, an obscure part of SQL that is of primary interest to database administrators (which is not what i am, so that pretty much exhausts my knowledge)

[Frunkie]

Originally Posted by r937

through the GRANT statement, an obscure part of SQL that is of primary interest to database administrators (which is not what i am, so that pretty much exhausts my knowledge)

Thank you Rudy

[Frunkie]

Well.. It’s been two days or thereabouts and I am still struggling with this problem.

Can someone please give me some ideas on how I can deal with existing rows in the database? Here is my big problem.

3 tables, A, B and C.

A holds the info on a person that calls in for a service such as their name, address and zip and is also the independent table to Tables B and C.

Table B holds their phone data, such as phone number, area code and what type of phone it is. Table C holds the type of service the person is requesting.

I am using transactions on all 3 of these tables. If there is duplicate data in any of them, the transaction will rollback which is exactly what I want to happen. My issue is that I have 1 form for tables A B and C and if there is duplicate data in any of the tables, no data is inserted into any of the tables and is rolled back. Again, I am fine with this too.

Am I correct in that I would have to query the db FIRST to see if a person already exists then display that data to the user for a choice before allowing a new row of data to be inserted?

The way I see it, I should have only 1 row of data per caller so if "Andy Brown" called in 50x in 1 month, I should have only 1 Andy Brown in table A and multiple corresponding rows in tables B and C.

I need some ideas as to how I can solve this. I thought it was going to be as simple as coding a single page for the inserts and updates but I am starting to see that data integrity puts a LOT MORE demands on the programmer unless I am missing something (And I hope I am). If this IS the case, I have just extended this project by about another 8 months because of all the tables I have with unique indexes.

Please, someone give me some design concepts here; I'm out of ideas and I’m really scared.

[r937]

Originally Posted by fjm1967

Am I correct in that I would have to query the db FIRST to see if a person already exists then display that data to the user for a choice before allowing a new row of data to be inserted?

no, you would not have to -- in fact, doing the work of the database seems counterproductive

why not let the database tell you if a dupe exists?

Originally Posted by fjm1967

I need some ideas as to how I can solve this.

not really sure what the problem is


but don't be scared -- ensuring data integrity might seem daunting, but it's a lot better than not doing it at all

[Frunkie]

Originally Posted by r937

no, you would not have to -- in fact, doing the work of the database seems counterproductive

why not let the database tell you if a dupe exists?


not really sure what the problem is


but don't be scared -- ensuring data integrity might seem daunting, but it's a lot better than not doing it at all

Thank you Rudy for helping. Ok let me give you a senerio.

Andy Brown calls in to the company and wants to request service. The dispatcher takes the call and imputs it into the db. That call is tied to Andy Brown as the caller.

If the db throws an error saying that there is already an existing Andy Brown, the dispatcher must be able to select that Anby Brown to tie him to the call. See?

So the dupe errors are really only half of the problem. Don't I need a way to allow the dispatcher to select the caller if they already exist?

[r937]

from that description, yes, you need a dropdown list or summat

on the other hand, you could simply add everybody as is

see, the way i look at it, designing the database logic comes after you design the user interface

maybe you could do it the way the pizza joints do it -- when you call to order a pizza to be delivered, they have your phone number, and ask you to confirm if you want it delivered to the same place as last time

[Frunkie]

Originally Posted by r937

from that description, yes, you need a dropdown list or summat

on the other hand, you could simply add everybody as is

Do you mean by removing the table constraints?

see, the way i look at it, designing the database logic comes after you design the user interface

I think you have a valid point here. Live and learn.

maybe you could do it the way the pizza joints do it -- when you call to order a pizza to be delivered, they have your phone number, and ask you to confirm if you want it delivered to the same place as last time

I wouldn't mind doing that either. This was actually something I have already been thinking about and seems like the best option. Maybe you could help me get the rest of my head around the issue.

I have an AJAX module that will query the db and return the results as the user types; much like google or yahoo.

If I used that module on an input box for the person's address and I have 2 or more people that have called into the dispatch center, there will be two addresses or however many people exist in the db that reside at that address. Basically, the AJAX module only returns 1 field only from the db.

[r937]

shoulda mentioned the ajax module sooner

seems like you've already solved the probelm???

[Frunkie]

Originally Posted by r937

shoulda mentioned the ajax module sooner

seems like you've already solved the probelm???

Yeah Rudy, I am just going to take a couple of days off and mull it over. I have to code new boxes now for the results from the AJAX module. Man, it just never ends..

Rudy, thank you very much for your help!