In hospital software, suppose I have a doctor entity which **may or may not **of type employee, how can I design the db to make the best of llblgen?

If I use

EMP(EmpID,Code,Name,Salary) and Doctor(ID,Code,Name,Specialization,EMPID) then I will get data for doctor sometimes from EMP table and sometimes from doctor table (based on whether Doctor is EMP or not ) which i think is a bad design

May be I can use

Person (PersonID, Code, Name) and EMP (PersonID, Salary) and Doctor (PersonID, Specialization)

Can llblgen handle this without problems?

Both of the proposed designs can work with LLBLGen Pro. But both of them would be refrential relations not an inheritance relation.

EMP(EmpID,Code,Name,Salary) and Doctor(ID,Code,Name,Specialization,EMPID)

Can't be an inheritance relation, coz in an inheritance relation a Doctor can't exist without being an EMP. A Doctor would be a subType that can't exist alone without its SuperType.

Person (PersonID, Code, Name) and EMP (PersonID, Salary) and Doctor (PersonID, Specialization)

Can't be an inheritance relation, since sometimes you would need an EMP and a Doctor to refer to the same Person, and in Inheritance: one row in the SuperType table can have only one SubType instance.

Are u sure the second solution is not inheritance ? Doctor -> Person Nurse -> Person Emp -> Person

It seems ok for me to inherit multi subtypes from one base, but yes llblgen can't handle it atuo

    Dim n As New MD_NurseEntity(2)
    MsgBox(n.Code)                                    =55
    Dim d As New MD_DoctorEntity(2)
    MsgBox(d.Code)                                    =55
    n.Code = 33                 
    n.Save()
    MsgBox(n.Code)                                    =33
    MsgBox(d.Code)                                    =55 //Must Refetch to see changes
    d.Refetch()                                                  // in the same person
    MsgBox(d.Code)                                    =33 

Also i can't create Doctor and Nurse for the same Person from llblgen generated code when i define the inheritance between Doctor-Person and Nurse-Person in designer, i create them directly in db

Any workaround to this?

OK, I got what you mean, Doctor->Person and Nurse->Person is ok as inheritance , but if i try to define Emp->Person for a person who is a doctor then it is problem. it think the solution here is to not define inheritance between Emp->Person but Define PersonID in Emp Table and make uniuqe constraint on it.

If you have an expertise in HR modules, did this desing makes problem or it is ok.

Thanks alot

In general, inheritance shouldn't be used if the type difference is based on some 'role' for example, like a job title. The reason for this is that you then run into the issue where you have to change the type of an instance, which isn't possible: you have to re-insert the data under a new type. The example of employee-manager-boardmember isn't really good from a 'use inheritance if you see these entities' kind of perspective, it was merely used to illustrate the necessity for a hierarchy of type target per entity when it comes to relations compared to a hierarchy of targetperentityhierarchy.

Thanks for reply,

First I suggest you rearrange the forums by topics, because I think many questions are repeated, for example, separate topic for inheritance, win forms binding, asp binding and so on to save you a lot of repeated questions.

You are right of course, but in hospital software a Doctor/Nurse Entities is a must Doctor entity have for example Medical_Specialization_ID which is a (Not Null –FK) with Medical_Specialization Table so it makes sense to give a doctor a separate entity.

The only problem comes from HR – Employee point of view, some doctors are employees and takes salary, some are not and just use hospital to make their private operations,

But both types of doctors are FK in the Operation Table (SurgeonDoctorID) so again it makes sense to seperate a Doctor Entity

Now, what is the good design for HR-Employee table?

EMP (empID, code, name, jobID) where jobID is discriminator But what about all other properties of doctor entity that have real relation with other many tables

Or

Person (PersonID, code, Name)

Person->Doctor (1-1 and use llblgen inheritance PerEntity) Person->Nurse (1-1 and use llblgen inheritance PerEntity)

EMP (personID, Salary) no inheritance because it will not work with llblgen, just unique PersonID

Or both designs are wrong

Thanks a lot

sunnyman wrote:

Thanks for reply,

First I suggest you rearrange the forums by topics, because I think many questions are repeated, for example, separate topic for inheritance, win forms binding, asp binding and so on to save you a lot of repeated questions.

That has been brought up before, but it's often not clear which topic to pick for a given question if it touches multiple topics. So it will then lead to crossposts, wrong forum posts...

We now have almost 10,000 threads on this forum and over 40,000 messages. The only way people will find threads which relate to their problem is via the search. It's not doable to rely on 'look in that forum and you'll find threads you need' anymore, the # of threads and posts is too big.

You are right of course, but in hospital software a Doctor/Nurse Entities is a must Doctor entity have for example Medical_Specialization_ID which is a (Not Null –FK) with Medical_Specialization Table so it makes sense to give a doctor a separate entity.

True, though semantically, Doctor and Nurse can't be in the same inheritance chain, other than that they're siblings and inheriting from the same parent. I mean, nurses won't become doctors, and doctors often refuse to act like nurses .

The only problem comes from HR – Employee point of view, some doctors are employees and takes salary, some are not and just use hospital to make their private operations,

But both types of doctors are FK in the Operation Table (SurgeonDoctorID) so again it makes sense to seperate a Doctor Entity

Agreed.

Now, what is the good design for HR-Employee table?

EMP (empID, code, name, jobID) where jobID is discriminator But what about all other properties of doctor entity that have real relation with other many tables

Or

Person (PersonID, code, Name)

Person->Doctor (1-1 and use llblgen inheritance PerEntity) Person->Nurse (1-1 and use llblgen inheritance PerEntity)

EMP (personID, Salary) no inheritance because it will not work with llblgen, just unique PersonID Or both designs are wrong

Well, doctor has a relation others don't have. So I wouldn't opt for a hierarchy of type TargetPerEntityHierarchy. You could of course use that type, but it's harder to build FK integrity checks into the db for these. (you've to fall back on check constraints).

I'd go for person -> doctor and person-> nurse, IF you need to share a lot of attributes between nurse and doctor and for example also relations, then the same supertype for both would be useful (so you can re-use the table).