Referential Integrity and DataAssist Insert

I have 2 parent tables in my database = staff and people..

The staff table contains

staffID
personID (Primary Key in people table)


The people table contains

personID(foriegn key in staff table)
name
address
city
postcode
and so on


I structured the database in this way to have less data redundancy, as all categories of users personal details can be stored in one unique table "people". The problem I am having now is when I run through data assist wizard for my "staff" table, the insert and update page will only create a field for personID. I would need to display all the fields in the "people" table, so when I create/add a new staff member I can add there personal details and when I click submit both tables will be populated and referential integerity will still exist, i.e. staffID will bring up the correct personID.

Would anyone be able to tell me how to do this maunally after using the initial data assist wizard.


Many Thanks in advance

Since you will need to have a personID for the staff table you will need to ensure that you insert the person details first, then you can use the id from that table as an identifier for the staff table, you would then insert into the staff table with this new personID.

Since it looks like the staff member cannot be associated with more than a single person it may be easier to just have a staffID in the person table, unless not all persons are staff members. This would simplify the process while keeping the data related in the same way. If you go this route you will only need to have single insert and updates occurring on the DataAssist pages to ensure that you record all of the info.

If you have other requirements for this or have any other questions about how you would implement this with the multiple update or inserts on a page please let us know and we will be glad to help get you pointed in the rite direction.

This post has been deleted.

Hi Eric

Originally Said By: Eric Mittman

  Since you will need to have a personID for the staff table you will need to ensure that you insert the person details first, then you can use the id from that table as an identifier for the staff table, you would then insert into the staff table with this new personID.  

I would have to use this option quoted as not all users are staff members. I would also like to apply security assist, and this option is similar to the blue sky music security assist authenticate user tutorials so it will make it easier for me.

What i was thinking to do is to make sure that a person is in the people table before they are inserted into the staff table. The problem is how do I call the personID. In the data assist wizard, do i use a menu or a textfield. A textfield will allow the inputter to just input the ID number manually. Is this the right way to go?

Alternatively if I set the personID to a drop down menu during data assist wizard on the insert/ update page, when the inputter selects the id number from the drop down could I have a firstname and lastname textfield autopopulate (from people table) based on the id?

Finally if both are possible I would like to combine the two ideas by giving the inputter the option to choose the id number from a dynamic menu, or type the number in a textfield. Then if the Id exists it will auto-populate a firstname and lastname text field, just so the user in definately sure they have input the right personid number.

Sorry for the length of this comment, you may have an easier option for all of this.

Your help is much appreciated

A question about the Blue Sky Music tutorial: please help me remember if there was ever an interactive version of it. I keep thinking that there was but I cannot find it anywhere. I need to settle this in my mind before I loose it. Thanks.

For the Blue Sky Music tutorial you may be thinking of the Solution Recipe that we used to have for eCart. This is still available in the archived documentation section of the eCart support page.

Other than that it is just the tutorial and the sample files that are available from the download center.

In regards to getting the insert working properly you can have a select list that is populated from a recordset that selects all of the id's. If you would like to display some of the info from the users table for that user you can submit the form with the id selected and have a recordset that is filtered based on that value. You would then have access to that users details from this filtered recordset. You can just add them into any part of the page by expanding the recordset and dragging the value to the desired area or use the bind or insert button to apply the value to a text field.

This page would just be a basic insert into the staff table. You may want to do one more check though before you insert the new id, you should have another recordset that selects all from the staff table where the person id matches the currently selected one. If the recordset is not empty you would want to stop processing and return the user to the page with an error message saying the record is a duplicate one.

As for having the text field and select list that part is doable but you will need to customize the insert server behavior to check to see if the text field has a value, if it does then use that value, otherwise use the value from the select list for the personID.

This may not be as straight forward or as easy as you hoped it would be but it is all doable. If you have any questions about any part of this please let me know and I will be glad to get you pointed in the right direction.

Thanks so much Eric for your Help,

I have managed to get it to work by creating an additional recordset, adding an additional "person_category" table to filter my people table in my database.

The only thing I need to find out is if webassist products has a way to prompt if the same record is entered twice as you disscussed in your last post.

  You may want to do one more check though before you insert the new id, you should have another recordset that selects all from the staff table where the person id matches the currently selected one. If the recordset is not empty you would want to stop processing and return the user to the page with an error message saying the record is a duplicate one.  

I'm not too sure how my WHERE clause should look e.g WHERE people.person_id = ?. I'm not sure how to write the "currently selected" sql statement.

I'm also not too sure how to stop processing and return the user to an error page, does dataassist have a feature to do this?

Many Thanks

Checking for the duplicate record is not a feature that can be applied in the scenario you are discussing. This feature is part of Security Assist but just for the user registration.

To craft this yourself you would create a new recordset that looks at the columns in your staff table. In the filter portion you would need to specify the person_id = form value, then enter the name of your input that has the person id. This is the same field you would specify in the insert for the person_id.

You would then just put in your insert server behavior like normal. Once it is on the page you can just put an if statement around it that checks to see if there are any rows in this recordset to check for duplicates. If there are not any records in it then there are no duplicates and you can continue with the insert like normal. The if statement would look something like this:

<?php if (!($totalRows_rsDuplicates >= 1)){?>

your insert server behavior here

<?php } else { ?>

header("Location: " . $your_redirect_page);

<?php } ?>

In here if there is not any rows in the recordset the insert will occur like normal. If there are records in the recordset then you will be redirect to whatever page you specify in the header call. This is just a start of what it would look like for you, the specifics will probably be different for you but the structure and idea are the same. Getting this implemented will require some custom coding as it is not a supported feature of the tools.