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-12-11 : 23:24:01
|
Hi i'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.animalidinner join drugs on drugs.drugid = operations.drug1idinner 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 drugProblem 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.. |
|
|
|
|
|
|
|
Help on vet database design