loop insert versus transaction

[hconnorjr]

Hi,

I wonder if someone has some good ideas. I have a database table that has this key info. (Yes, I know its not normalized, but that's my goal now.)


PhysicianID
PhysicianSpecialty1
PhysicianSpecialty2


I think that my goal is something like this,

PhysicianID
PhysicianSpecialtyID


PhysicianSpecialtyID
PhysicianSpecialtyName


But each physician can have multiple specialties and I need to be able to enter all the data from one form at the same time and maintain referential integrity with the daughter table with all going in as a single transaction. Would a loop be appropriate here? A transaction? All help appreciated.

[Newsgroup_User]

hconnorjr wrote:
> I think that my goal is something like this,
>
> PhysicianID
> PhysicianSpecialtyID
>
>
> PhysicianSpecialtyID
> PhysicianSpecialtyName

What you need is a lookup table.

physicians
PhysicianID
PhysicianName

specialties
PhysicianSpecialtyID
PhysicianSpecialtyName

physician2specialty
PhysicianID
PhysicianSpecialtyID

The lookup table (physician2specialty) contains just two columns, and
both IDs are declared as a joint primary key. So let's say that
PhysicianSpecialtyID 1 is Nose jobs and PhysicianSpecialtyID 2 is Tummy
tucks. You have two physicians: Jones (PhysicianID 1) and Smith
(PhysicianID 2). The lookup table looks like this:

PhysicianID PhysicianSpecialtyID
1 2
2 1
2 2

Jones does just Tummy tucks, but Smith does both Nose jobs and Tummy tucks.

By declaring PhysicianID and PhysicianSpecialtyID as a joint primary
key, you ensure that the combination is always unique. In other words,
you cannot list the same specialty against an individual physician more
than once. When updating a physician, you delete all existing records
connected with that physician in the lookup table, and insert the
updated combination. So, if Jones starts doing Nose jobs, you delete all
records in the lookup table where PhysicianSpecialtyID = 1. Then

INSERT INTO physician2specialty (PhysicianID, PhysicianSpecialtyID)
VALUES (1,1),(1,2)

It sounds complicated, but is quite simple to implement.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[hconnorjr]

david,

thanks for the schema design help.

part of my question, I think, is very fundamental, which has to do with how you insert data into daughter fields that require more than one row of the database in one transaction. for example here, say physicianID =100 and I want to insert multiple physician specialties ALL AT ONCE. So my interface has text fields for up to six specialties. How can I insert that inputed data when I hit insert, so that the physician info table is referentially linked to each of the physician_specialtyID's. that's why i was thinking of a loop or a transaction that allows multiple inserts with a single physician_ID. maybe you can tell, i'm a beginner on the use of loops and transactions.

thanks.

[Newsgroup_User]

hconnorjr wrote:
> part of my question, I think, is very fundamental, which has to do with how
> you insert data into daughter fields that require more than one row of the
> database in one transaction. for example here, say physicianID =100 and I want
> to insert multiple physician specialties ALL AT ONCE.

As I said before, it's not all that difficult. Basically, you use a
select drop-down menu to display the names of the physicians and set the
value of each option to the physician's ID. You also use multiple-choice
list to display the names of the specialities - again with the value of
each option set to the ID. The values from the multiple-choice list need
to be sent as an array, which you use to build the INSERT or UPDATE query.

That's the basic principle, but it's not something that can be explained
in a simple forum post. I give step-by-step details in Chapter 14 of
"PHP Solutions", but it takes 10 pages of a printed book to go through
the full explanation.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[hconnorjr]

david,

i read your other book, the foundation php and dreamweaver which hits the sweet spot for avid beginners. in php solutions, i'm impressed that it takes 10 pages to explain all of this, more detail is better. i'll go out and find it.

[Newsgroup_User]

hconnorjr wrote:
> i read your other book, the foundation php and dreamweaver which hits the
> sweet spot for avid beginners. in php solutions, i'm impressed that it takes 10
> pages to explain all of this, more detail is better. i'll go out and find it.

I hope it helps sort things out for you. Feel free to ask questions
about anything that's unclear.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[hconnorjr]

hi again,

i got the php solutions book: dynamic web design and its a good beginners- intermediate guide. it does well with many to many fields. i liked two tips especially:
1. there is no autoincrement ID key in a m-t-m link table.
2. what happens if you want to make a choice that is not displayed in a link table design . . . you have to go back and update one of the tables. if you have a dynamic display of that table, as soon as you finish updating the table, the new choices should be displayed. it makes working with a link table a lot easier and was a missing piece for me. nice book.

[Newsgroup_User]

hconnorjr wrote:
> hi again,
>
> i got the php solutions book: dynamic web design and its a good beginners-
> intermediate guide. it does well with many to many fields.

> it makes working with a link table a lot
> easier and was a missing piece for me. nice book.

Glad you like it. Thanks for the feedback.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/