"Why can't you create a MR without any patients "attached" to it?"
MR is like an identification for each familiy, and each familiy consist of several patient..
does my erd is already correct? and if, for example, i want to add a new MR with new patients of course, so i have to do :
insert into havepatient(MRId, patientId) values (NULL, NULL);
insert into MR(MRId, regDate) values (1, 2007/04/05);
insert into patient(patientId, fname, lname) values(1, lala, baba);
is it correct? and is it efficient to perform 3 inserts just for adding 1 new MR?
sorry for asking the same problem againn..
so, i write something like this:
$queryMR= "INSERT INTO MR (MRId, time) values (NULL, current_timestamp())";
$queryPatient= "INSERT INTO patient ( patientId , fname ,lname)";
$queryPatient.= "VALUES (NULL , '$firstname', '$lastname')";
$queryHave_Relation= "INSERT INTO havePatient(patientId, MRId) SELECT p.patientId, m.MRId";
$queryHave_Relation.= "FROM patient p, MR m WHERE m.MRId='' and (p.fname= '$firstname' and p.lname= '$lastname')";
my question is what should i put in the MRId= '' (the red one), i want to take the result of MRId from $resultMR, but if i put the MRId straightaway (hardcode) then it is useless.. i have thought about taking the latest MRId from the MR table, but the problem is there are more than 1 user that perform insertion in the same time.
can anybody help me out from this problem? =)
thank's for ur attention..
One thing I noticed straight away is that you're insering a NULL value as your MRId!! You should NOT have null values for an identity field like this - and as your patientId! If these are autonumbers you can simply ignore them in an insert as they will be populated automatically.
Secondl; according to your 3rd query you are searching for patients' first names and last names as a means of joining the two... Bad move...
What if you have 10 people called John Smith?
Also, after inserting a record, (for SQL Server) you may get the value of your most recently inserted value by using the @@identity function
INSERT INTO MR (time) values (current_timestamp())
SELECT TOP 1 @@identity as LastRecordID from MR
This will return the last identity value in your connection. Even if another users inserts a record after you've inserted the record, but before you select @@identity, the value you receive is the value associated with your insert.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert