Help on vet database design

Question

Help on vet database design

skinothetis1

SSC Enthusiast

Points: 108
More actions
December 12, 2009 at 10:19 am

#214536

Hi.First post hope to stay here a long time 🙂
Ι'm creating a veterinarian database. (sorry for the size of the post in advance).
So i have a problem on the drugs the vet use, design.
The drugs of an operation can be many and i would need to associate the drug id with a drug table so i can get it's name,so i'm thinking 4 alternatives.
1)An operations table that will have the animalid and a finite amount of drug columns. Like drug1,drug2,drug3 etc. On this design i don't think i can create FK to the drug table because it would need as many relationships as the drugs, so i'm thinking of creating the: table animals - id FK table operations - animalsid .Inner joining to find the drug name...Problem, if a drug is null then i cannot do an inner join (will clear the data)
SELECT *
FROM Animals INNER JOIN
operations ON Animals.id = operations.animalid
inner join drugs on drugs.drugid = operations.drug1id
inner join drugs as dr2 on dr2.drugid = operations.drug2id
--- drug3 was null so i got null rows
--inner join drugs as dr3 on dr3.drugid = operations.drug3id
Any help on that or i will get nulls if a null drug exists?
2)The second solution i was thinking was to have one new row on the operations table for every single drug that is used.
So i use, lets say 2 drugs, i get 2 rows, i use 12 i get 12 rows.
So i will have an insert for all the rows with union all or:
INSERT INTO operations (animalfk,drug1) VALUES
(@fkval, @drug1val)
INSERT INTO operations (animalfk,drug2) VALUES
(@fkval, @drug2val)
etc, until my last drug
Problem here: What if i have only 2 drugs? Then i should not do an insert until my last drug cuz i will get 2 rows with the 2 drugs and all the other rows will be empty and only have the animalfk.So must i create (lets say i have 12 drugs), 12 stored procedures that will increment the drugs by 1 and based on the amount of drugs, i will decide what to use? (first sp inserts first drug on the list and all other nulls, second sp inserts first and second drug on the list and all other nulls , etc).
3)I was thinking of creating a list of drugs on a column.I have no idea how to get the values from the list, insert a list to a column and associate the drug id's so i can get the names.
4)Give the finger to the operations.drug1id,etc and just do a select on the drug table, get all the drug names and put them in the operations table. This should produce something like: 1,2,mydrug,animaldrugx,animaldrug23,yourdrug (columns id,operationid,drugname1,drugname2,drugname3 etc...)
Problem here, i have no association.
So that's it.I know it's big but if you have reached here i appreciate it and will appreciate it more if you give me a solution and even better, a best solution for my problem.
Thanks..

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

I cannot follow you table notation(?) and your descriptions of table and relations is pretty confusing. plus you seem to be assuming that we already know all kinds of things about your data that you haven't told us.

Most important here, is what is the real-world relationship between Animals and Drugs that you are trying to model in your data?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 738743 More actions · Answer 2

I agree with Barry that you haven't really presented your thoughts in an organized manner and it's hard to follow.

My guess is that you would have an DrugOperations table that contains an operation FK and a drug ID. You would link the drug to the operation, not the animal. If you have multiple animals in an operation, meaning that's a possibility, then I would include the operation ID and an animal ID. However an operation, to me, is like a sale. It's a single event/entity. The associated items, like animals, location(s), drugs, doctors, etc are links and may require separate tables.

skinothetis1 SSC Enthusiast Points: 108 More actions · Answer 3

Hi.

Sorry if i have confused you and thanks for answering. Actually i know what i want to do but i'm not sure how.

Ok i'll try to be sort.

I will have an animal table, an animaloperations table and a drugs table.

Animal will fk with animaloperations, that's certain, in order to know the animal that is doing the surgery-operation.An animal can have many operations and it will be one and only one animal for an operation.

Now the difficult part:

The operation may require more than one drug, so, ok, i'll have a table with the drugs.The problem is how to link 1,2,3,10 or more drugs to the operation table.So my thoughts are on the 4 solutions i've posted.And to be more specific the whole animal-operation-drug will go to an asp formview and the drugs must be inside a dropdownlist.The immediate problem that i see is that if i have many rows (one, animal-operation-drug row, for each drug) then if i bind the formview i would probably end up with many pages of the same operation(with a different drug at each page).

So to narrow it down the whole problem is how to have one page form with my operation details and have a dropdownlist with all the drugs used (not the id but the name, of course), that's why i was thinking of a list column that will contain the drugs used but i don't think i can create an FK with the drug table...

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

Option 2 without most of the stuff you mentioned.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

This link seems appropriate. I quote "Whenever I see a table with repeating column names appended with numbers, I cringe in horror".

http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

Clear Sky SQL
My Blog[/url]

skinothetis1 SSC Enthusiast Points: 108 More actions · Answer 6

Thanks guys.Especially the article was very helpful.

So here is what (it think) must be done.

An animals table, An operations table, a drugs table and an intermediate table, lets call it drugsoperations.

Animal will FK with operations on Animalid that's the standard, now:

drugsopertions will have an operationsid that will FK with operations.id, finally drug.id will be PK on a relationship of drugsoperations.drugid - drugs.id.

So the drugsoperations table will serve as an intermediate between operations and drugs. So in it's simplest form will have id,drugid(FK to drug.id),operationsid(FK to operations.id).

Please let me know if this a correct way.

Of course my problems will then relay on the asp.net that i will need to have 2 data connections (The one for the regular Formview and the other for the combobox).I see many problems there but i will see what i can do.

But please give quote so i will be ok on design part.

Thanks.

P.S. Any way i can rep you guys? 🙂

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

skinothetis1 (12/13/2009)
Thanks guys.Especially the article was very helpful.
So here is what (it think) must be done.
An animals table, An operations table, a drugs table and an intermediate table, lets call it drugsoperations.
Animal will FK with operations on Animalid that's the standard, now:
drugsopertions will have an operationsid that will FK with operations.id, finally drug.id will be PK on a relationship of drugsoperations.drugid - drugs.id.
So the drugsoperations table will serve as an intermediate between operations and drugs. So in it's simplest form will have id,drugid(FK to drug.id),operationsid(FK to operations.id).
Please let me know if this a correct way.

Based on what you have told us, this seems correct.

But please give quote so i will be ok on design part.

Huh?!?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

skinothetis1 SSC Enthusiast Points: 108 More actions · Answer 8

Thanks!!

Sorry i wanted to say please replay, or something 😀

Anyway i'm from Greece so English is not my mother language (although English is a mixture of Greek and Latin).

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

skinothetis1 (12/14/2009)
Thanks!!

Glad I could help.

...Anyway i'm from Greece so English is not my mother language (although English is a mixture of Greek and Latin).

Well, while it's true that English has many words with Latin or Greek roots, it's really not correct to say that it's a mixture of those two. It is much more a mixture of Anglo/Germanic/Norse and Saxonese.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 10

Have a look at this, it may be what you are looking for:-

http://www.databaseanswers.org/data_models/vets_practice/index.htm

skinothetis1 SSC Enthusiast Points: 108 More actions · Answer 11

Thanks Ian i could pick up ideas for this model.I don't know if they need financial info but i will be prepared 🙂

Barry, actually i wouldn't want to sound like a Greek fanatic of something but you have thousands of words that you don't know as Greek but they are actually Greek.

A university professor wrote, if i remember correctly, a 10000 words essay in English that was only included Greek root words.I'll try to find it if you're interested.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

skinothetis1 (12/16/2009)
...Barry, actually i wouldn't want to sound like a Greek fanatic of something but you have thousands of words that you don't know as Greek but they are actually Greek...

Why would you claim that I don't know a Greek word word from a non-Greek one? That's a bit rude, and I assure you that I do.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

skinothetis1 SSC Enthusiast Points: 108 More actions · Answer 13

Hi.

Sorry i did not want to be rude.What i meant was that many Latin or other language words are actually Greek based.That's all.

Sorry if i offended you.