March 4, 2004 at 2:26 pm
Hi
For my project i am trying to create an online registering system. The users enter all of the required info through the form it is then handled on the server by asp scripts.
I have to tables resident and household
resident table:
----------------------------------------
Personal_ID (PK) | Name | DOB | Household_ID
Household table:
----------------------------------------
Household_ID (PK) | Address | Post Code
The problem i am having is that when i add a new record into the database i have to add details into both tables, I have to increment both the Person_ID and the Household_ID by 1 (as were not allowed to use auto-no.) Which i can do HOWEVER there can be many residents living in the same house. e.g 2 people could both live at the same address and therefore both need the same Household_ID, i am not sure how to do this. Here is my code:
'Increment person_ID column by 1 in resident table
sqlQuery2 = "SELECT MAX(Person_ID) As personID FROM resident"
Set recordSet = connection.Execute(sqlQuery2)
lastRecord = (recordSet("personID")+1)
'Increment household_ID column by 1 in resident table (also used in household table)
sqlQuery3 = "SELECT MAX(Household_ID) As houseID FROM resident"
Set recordSet2 = connection.Execute(sqlQuery3)
lastRecord2 = (recordSet2("houseID")+1)
'Insert values into the tables
sqlQuery1 = "INSERT INTO resident VALUES(" & lastRecord & ", '" & fullName & "', '" & dob & "', " & lastRecord2 & ")"
Set recordSet3 = connection.Execute(sqlQuery1)
sqlQuery4 = "INSERT INTO Household VALUES(" & lastRecord2 & ", '" & addressID & "', '" & fullPostCode & "')"
Set recordSet4 = connection.Execute(sqlQuery4)
Hope this makes sense. Any help would be appreciated.
Thanx
Dan.
March 5, 2004 at 10:59 am
When you add a new person you need to add to the resident table and MAYBE add to the household table. You do not add to the household table if the household exists already. So check first if the address is already in this table. If so just get the household id and use that in the INSERT for the resident table. If not add the records as in your post.
BTW if you have one column for address how you you know that '21 Main street' and '21 Main St.' are really the same household?
Francis
March 5, 2004 at 1:57 pm
Why don't you just add another column to household and call it "resident_id" as a FK instead?
March 5, 2004 at 2:06 pm
Adding resident_id to the household table would denormalize the table, since now we must repeat the address for each resident, potentially leading to inconsistant data.
Francis
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply