Whats the correct way to make an insert into two related tables at once.

eg I have 3 tables, user, user_course and course.

When a user is created they may be assigned to a course immediately,

therefore this implies

2 inserts at once BUT PK's and FK's of either table are needed at once.

EG

Insert into user - user_id(creates an autonumber - user_id) name,

user_course_id etc

Insert into user_course user_id,(creates an autonumber - user_course_id)

user_id, course_id etc



The problem obviously is with the first insert we dont know the FK

user_course_id, and therefore

would have to insert into the new User row, leaving the user_course_id field

blank and updating the row after the second

insert into user_course when we have the new user_course_id.



Can anyone tell me a more efficient way of doing this rather than 2 inserts

and an update please?



Thanks



Louise





************************************************************ 

This email and any files transmitted with it are confidential and intended

solely for the use of the individual or entity to whom they are addressed.

If you have received this email in error please notify the system manager.



www.compupharma.net 

************************************************************ 

Louise,



From your description you have the PK of the users table in the

users_courses table, and the PK of the users_courses table in the users

table. That is *not* how you design a database.



My guess is that what you want is:

Users: PK = UserID

Courses: PK = CoursesID

Users_Courses: PK is a combination of UserID and CoursesID, both are FK

referencing the PK of the Users and Courses tables respectively.



To get this all to work, you insert a record into the Users table, get the

resulting autonumber, then you insert into the Courses table (if required),

get the autonumber, and then you insert into the Users_Courses table.



If you need code to get the new autonumber:

http://www.adOpenStatic.com/experiments/fastestautonumber.asp



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Louise Greally" <lgreally@c...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, May 30, 2001 7:05 PM

Subject: [asp_databases] 2 inserts into related tables at once





: Whats the correct way to make an insert into two related tables at once.

: eg I have 3 tables, user, user_course and course.

: When a user is created they may be assigned to a course immediately,

: therefore this implies

: 2 inserts at once BUT PK's and FK's of either table are needed at once.

: EG

: Insert into user - user_id(creates an autonumber - user_id) name,

: user_course_id etc

: Insert into user_course user_id,(creates an autonumber - user_course_id)

: user_id, course_id etc

:

: The problem obviously is with the first insert we dont know the FK

: user_course_id, and therefore

: would have to insert into the new User row, leaving the user_course_id

field

: blank and updating the row after the second

: insert into user_course when we have the new user_course_id.

:

: Can anyone tell me a more efficient way of doing this rather than 2

inserts

: and an update please?

:

: Thanks

:

: Louise

.com

Thansk for your reply.

Perhaps I do have the design incorrect.  But the way Ive envisaged the

tables being used is that a user can take many courses, and the

user_course_id that is retained in the user table is the current course 

the

user is actively taking, does that make sense?

I  pass the course_id, user_id into the user_course table and the new 

row

generates the user_course_id. 

Actually another thing is that a user may take the same course twice 

i.e

user_id 101 may take course 202, more than once. However

they will have different login, assessment records associated with that

instance of taking the course, i.e. related to the user_course_id.



As you said maybe I created the tables wrong, but I need a logical way 

of

allowing users to take more than one instance of a course.

Any help would be most appreciated even if its to clarify that the 

logic of

my db design is still incorrect.



Regards

Louise



-----Original Message-----

From: Ken Schaefer [mailto:ken@a...]

Sent: 30 May 2001 13:52

To: ASP Databases

Subject: [asp_databases] Re: 2 inserts into related tables at once





Louise,



From your description you have the PK of the users table in the

users_courses table, and the PK of the users_courses table in the users

table. That is *not* how you design a database.



My guess is that what you want is:

Users: PK =3D UserID

Courses: PK =3D CoursesID

Users_Courses: PK is a combination of UserID and CoursesID, both are FK

referencing the PK of the Users and Courses tables respectively.



To get this all to work, you insert a record into the Users table, get 

the

resulting autonumber, then you insert into the Courses table (if 

required),

get the autonumber, and then you insert into the Users_Courses table.



If you need code to get the new autonumber:

http://www.adOpenStatic.com/experiments/fastestautonumber.asp



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Louise Greally" <lgreally@c...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, May 30, 2001 7:05 PM

Subject: [asp_databases] 2 inserts into related tables at once





: Whats the correct way to make an insert into two related tables at 

once.

: eg I have 3 tables, user, user_course and course.

: When a user is created they may be assigned to a course immediately,

: therefore this implies

: 2 inserts at once BUT PK's and FK's of either table are needed at 

once.

: EG

: Insert into user - user_id(creates an autonumber - user_id) name,

: user_course_id etc

: Insert into user_course user_id,(creates an autonumber - 

user_course_id)

: user_id, course_id etc

:

: The problem obviously is with the first insert we dont know the FK

: user_course_id, and therefore

: would have to insert into the new User row, leaving the 

user_course_id

field

: blank and updating the row after the second

: insert into user_course when we have the new user_course_id.

:

: Can anyone tell me a more efficient way of doing this rather than 2

inserts

: and an update please?

:

: Thanks

:

: Louise

.com

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: Thansk for your reply.

: Perhaps I do have the design incorrect.  But the way Ive envisaged the

: tables being used is that a user can take many courses, and the

: user_course_id that is retained in the user table is the current course

the

: user is actively taking, does that make sense?

: I  pass the course_id, user_id into the user_course table and the new row

: generates the user_course_id.

: Actually another thing is that a user may take the same course twice i.e

: user_id 101 may take course 202, more than once. However

: they will have different login, assessment records associated with that

: instance of taking the course, i.e. related to the user_course_id.

:

: As you said maybe I created the tables wrong, but I need a logical way of

: allowing users to take more than one instance of a course.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Your ER would say something like:



Entity: Users

Entity: Courses



Each User can undertake many courses, only 1 of which would be active

Each Course can be undertaken by many users.



Since you have an M:N relationship you need a join table: Users_Courses

Since you need to differentiate current courses from previous courses, have

a column in the Users_Courses table called "Active" (or similar). This is

set to 1 for the course that the user is currently undertaking, and 0 for

every other course that the user has done.



Since the user can undertake each course more than once the Users_Courses

psuedo-entity then a combination of UserID + CourseID does not uniquely

identify each record in that table, hence it can not be a candidate Primary

Key. Instead, add an autonumber to the Users_Courses table. Alternatively,

if you have separate course records for each year/session (eg:



CourseID         CourseName        Year

    1                 Accounting1        1999

    2                 Accounting1        2000

    3                 Accounting1        2001



then you don't need an autonumber in the Users_Courses table. I don't know

how your courses table is set up, so I'm not sure which is the way to go.



You might want to pick up a database design book, something that covers ER

modelling.



Cheers

Ken