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 |
|
sapator
Constraint Violating Yak Guru
462 Posts |
 Posted - 2009-10-11 : 19:37:13
|
| Hi.I'm writing (for the time being on paper) a veterinarians database.So i would appreciate an opinion on the below subject.For the tables that matter, i have a doctors table, an owner table, and some animal tables.What i will probably do is connect the doctor table with the animal(on animal.doctorid) and the owner table with the animal(on animal.ownerid).So my thought is how to have an interaction on the doctor and owner table.The simplest i can think is through the animal table but the problem here is that owners can change doctors so i was thinking of a table that could have,p.e., a doctor id column, and an owner id column.Fk's on doctors table(doctor id on doctor id) and owners table (owner id on owner id).On the other hand i was thinking that if the animal table has a doctor id and owner id.If p.e. the doctor id on the animal table changes then supposedly the owner has changed doctor. If the owner id changes then the animal has a new owner (but same doctor) and if both changes then the animal has a new doctor and a new owner.Any thoughts would be appreciated. |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-10-11 : 19:39:27
|
| Also there is a possibility that the previous doctor could keep a file of the animal patient till the day the pet goes to another doctor...Now that i'm currently....Thinking.... |
 |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-10-12 : 12:20:34
|
| So any opinions?I'm thinking more of the all id's in animal table approach. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-10-12 : 14:27:32
|
| You might need to have the information as of a point in time. An animal could change doctor or owner.It also seems possible to have an animal without an owner and/or without a doctor, or to have multiple owners or doctors.CODO ERGO SUM |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-10-12 : 21:27:01
|
| Well changing doctor or owner look more easy to do when i put the owner,doctor id's to the animal table. Yes there could be no owner (possibly there must be a doctor always), so i could set the column (owner id) to allow nulls.Hmm, multiple owners or doctors...I have to take a note on this but i think it won't be the case.Also another task is to see (as you said) when an animal change a doctor or owner...I'm thinking off another table with animal,doctor,owner id and new rows when a change occurs + date (doctor,owner could be read/write) but if i do that i will no longer use the id's on the animal table )doctor,owner), or possibly to update the 2 tables?... For the animal patient i'm thinking another table with the "treatment" info that will definitely include that date that this happend.That could allow for a doctor to keep the files till that day the animal changes doctor. |
|
|
|
|
|
|
|
|
Opinions on vet database.