Help on vet database design

Question

Help on vet database design

Viewing 13 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: 739541 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: 1004664 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.