Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
lonifasiko
Starting Member
11 Posts |
Posted - 2005-08-31 : 05:16:10
|
Hi all,My problem is around 5 tables of my database:1) Table "Patient" with patientId (GUID) as primary key.2) Table "Doctor" with doctorId (GUID) as primary key.3) Table "Nurse" with nurseId (GUID) as primary key.4) Table "Relative" with relativeId (GUID) as primary key.5) Table "UserRole" which contains: - username (Primary key) - password - role (values: "Patient", "Doctor", "Nurse" or "Relative") - personalId (must exist it's corresponding record in only one of the four tables listed above).When I create a patient, I must also give him a username and pass to access the application. Each of the four entities will only have one record in UserRole table. Therefore, the relationship is 1 to 1.The problem is I cannot make relationships between UserRole-Doctor, UseRole-Nurse, UserRole-Patient and UserRole-Relative. Why? Because if I try to insert a doctor's id in personalId field of UserRole, referencial integrity of UserRole-Nurse, UserRole-Patient and UserRole-Relative bumps because that value does not exist in neither Nurse nor Patient nor Relative tables.This issue must have a very simple answer but I'me getting mad on this. I've tried with check constraints from SQL Server Enterprise Manager but gives me some errors when making expressions of this kind: ------------------------------------------------personalId = (Select doctorId from Doctor) OR personalId = (select nurseId from Nurse) OR personalId = (select patientId from Patient) OR personalId = (select relativeId from Relative)--------------------------------------------------Can you seed me some ligth on this please? Thanks in advance. |
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-08-31 : 05:32:12
|
1) Try embedding the expression in a UDF, then use the UDF in a CHECK constraint2) Create a View using the WITH CHECK OPTIONDavidMA front-end is something that tries to violate a back-end. |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-08-31 : 07:24:40
|
What if you create a UNIQUE constraint on personalId in UserRole table,and reverse the FK constraints.You could also remove the role column. If a personalId has a corresponding record in e.g. the Patient table, then that user has a Patient role.If you don't use any further constraints, a user can then have multiple roles.(Albeit with the same username & password) |
|
|
lonifasiko
Starting Member
11 Posts |
Posted - 2005-08-31 : 07:30:15
|
Thanks for the reply.I've created an UDF function (called checkPersonalId) that returns me 1 if the record exists and 0 if not. Function works weel because I've tried with Query Analyzer.How do I add the check constraint to a column in SQL Server. What should be the expression? This way:(select checkPersonaId(personalId)) = 1Does not work. Any other ideas? SHould be very simple.------------MIGUELÓN |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-08-31 : 07:34:01
|
quote: 1) Try embedding the expression in a UDF, then use the UDF in a CHECK constraint2) Create a View using the WITH CHECK OPTION
3) Consolidate the 4 tables into one, and add a column for PersonType to store "Doctor", "Patient", "Nurse" or "Relative". These tables are all storing the same type of information, they should be in one table. If you have to support a legacy app that needs the original table names, create views with the same name that would return the same information:CREATE VIEW Doctor AS SELECT PersonID as DoctorID, Name FROM Person WHERE PersonType='Doctor'CREATE VIEW Patient AS SELECT PersonID as PatientID, Name FROM Person WHERE PersonType='Patient'...etc.The UserRole table will have a foreign key to the new Person table in any event. |
|
|
lonifasiko
Starting Member
11 Posts |
Posted - 2005-09-01 : 03:20:40
|
Hi again guys, byrmol: finally achieved calling the UDF from a check contraint clause this way: dbo.checkpersonalId(personalId) = 1Thanks very much.rockmoose: I have a unique contraint on personalId. What do you exactly mean by "reverse FK contraints". Could you explain a little bit more please? Role column is needed because to extract information related to a patient, I need to know what table I must query. And this information is only in Role column. I cannot query one table behind another till I fin the corresponding record in e.g. Patient table.robvolk: I deeply thought about the idea of having one unique table but the information of a Patient is far different from the information stored for a Doctor and so on, therefore I finally decided to have separate tables.Thanks very much to all. I would be glad in continuing receiving more new ideas or points of view from you. Regards.------------MIGUELÓN |
|
|
HCLollo
Starting Member
49 Posts |
Posted - 2005-09-01 : 09:29:12
|
Hi lonifasiko!Why don't you try to mix what you're already doing with what robvolksuggested? Like storing all common info in one table, and addingother tables like PatientExtraInfo, DoctorExtraInfo, etc.? So youwon't be bothered by unique ID thing and could keep track of thingslike, for example, a doctor getting sick and becoming a patient HCL"If it works fine, then it doesn't have enough features" |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-09-01 : 11:54:01
|
Example;This maintains RI and 1 role membership.It is not possible to update the role in the UserRole table, and You can't insert a Doctor into Patient table.Looks a bit messy I know , but it's failsafe.You have to first insert the "UserRole" then the specifics in the correct Doctor/Patient/Relative table.UserRole( username primary key ,password ,role check(role in('Patient', 'Doctor', 'Nurse' or 'Relative')) ,personalId unique ,common_stuff ,unique(personalId,role))Doctor( doctorId primary key ,role check(role = 'Doctor') ,doctorinfo_stuff ,foreign key FK_UserRole_Doctor(doctorId,role) references UserRole(personalId,role))Patient( patientId primary key ,role check(role = 'Patient') ,patientinfo_stuff ,foreign key FK_UserRole_Patient(patientId,role) references UserRole(personalId,role)) |
|
|
lonifasiko
Starting Member
11 Posts |
Posted - 2005-09-02 : 02:39:00
|
Thanks very much for your time and deep explanation (example included! ).I have now understood your point of view. The unique thing I dislike from your approach is to have to insert first in the UserRole table instead fo the Patient/Doctor table. A patient's data could be inserted in the database but momentaneously without a username to access the application. Besides, patientId, doctorId...primary keys are GUID-s, and are generated by default with newid() function, so I have to generate them before inserting in UserRole.Oh my God! I think I'm a bit dizzy! You mean using newid() for UserRole table and using that GUID for inserting in the other tables, without using newid() function on this tables, don't you?I have now my fingers in a lot of pies but I promise you I'll try your solution in a few days and post my results here soon.See you guys, thanks again and regards.------------MIGUELÓN |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-09-02 : 18:05:39
|
quote: Originally posted by lonifasiko Thanks very much for your time and deep explanation (example included! ).I have now understood your point of view. The unique thing I dislike from your approach is to have to insert first in the UserRole table instead fo the Patient/Doctor table. A patient's data could be inserted in the database but momentaneously without a username to access the application. Besides, patientId, doctorId...primary keys are GUID-s, and are generated by default with newid() function, so I have to generate them before inserting in UserRole.Oh my God! I think I'm a bit dizzy! You mean using newid() for UserRole table and using that GUID for inserting in the other tables, without using newid() function on this tables, don't you?I have now my fingers in a lot of pies but I promise you I'll try your solution in a few days and post my results here soon.See you guys, thanks again and regards.------------MIGUELÓN
You are correct.Here is a rephrasing of the two approaches.First approach (yours + elaborated on by other posters)-------------------------------------------------------UserRole is a subclass of : the "union type" {Doctor Union Patient Union Relative}UserRole inherits the attributes of one of the superclasses in the "union type" (the one it belongs to).The key is created in one of the superclasses Doctor, Patient, Relative and is FK into UserRole.Second approach (my example)----------------------------Doctor, Patient, Relative are subclasses (specializations) of UserRole.Doctor, Patient, Relative inherits all the attributes of the superclass UserRole.The Key is created in the superclass UserRole and is FK into one of the specializations Doctor, Patient, Relative.Just offered as food for thought and discussion...rockmoose |
|
|
|
|
|
|
|