Better Modeling Methods ?

Want to model the following two items: Can anyone think of better solutions
?
******************
* Address *
******************
Here we have an Address which could relate to a Company or an Individual, bu
t the same address will never relate to both company and indvidual. (this is
theory to keep the proble simple). In this senerio, a Company and an Indivi
dual will have different ad
dresses sometimes, and the same address sometimes, and two or more Individua
ls could also have the same address.
Tables
--
Company
Individual
Address
- Possible Solution A:-
--
Make Company and Individual Primary Keys Globally Unique and map them both t
o the same foreign key, in one Address table.
Company
--
CompanyId (GUID) --|
|
|
|
|
Individual |
-- |
IndividualId (GUID) --|
| Address
| --
+--> EntityId
- Possible Solution B:-
--
Make two address tables - store the exact same kind of data in two different
locations - ;(
Company
--
CompanyId (GUID) --|
|
| CompanyAddress
| --
+--> CompanyId
Individual |
-- |
IndividualId (GUID) --|
| IndividualAddress
| --
+--> IndividualId
*********************
* Drug Allergys *
*********************
Here we will be modling drugs and their associated allergys, based upon both
Ingredient and Route (method of drug administration i.e. Oral,IV,Suppositor
y). In this case, I will have to check both Ingredient and Route OR Ingredi
ent ONLY OR Route ONLY to s
ee if these has been a drug Allergy (in the real world many many items and c
ombinations there of).
Tables
--
Drug
Ingredient
Route
Allergy
- Possible Solution A:-
--
Place all drug descriptors (Ingredient, Route etc) in the Allergy table as f
oreign keys.
Drug
--
DrugId --+
| IngredientMap
| -- Ingredient
| IngredientMapId --
| IngredientId <-- IngredientId --+
+->DrugId Ingredient |
| | Allergy
| RouteMap | --
| -- Route | AllergyId
| RouteMapId -- +-> IngredientId
| RouteId <-- RouteId --> RouteId
+->DrugId Route
This problem differs from the Address problem, in that I can not create uniq
ue keys, because I need to look at all "drug descriptors" (Ingredient,Route
etc.) at the same time, and therefore mappling all "drug descriptors to one
foreign key does not seem t
o be convienient.
In this senerio, I have to check all columns in Allergy (WHERE clause), ALL
THE TIME, in order to get the correct results. For Example:
SELECT AllergyId
FROM Allergy
WHERE IngredientId IN (
SELECT IngredientId
FROM IngredientMap
WHERE DrugId = X
) AND
RouteId IN (
SELECT Route
FROM RouteMap
WHERE DrugId = X
)Hi
For the first scenario you can have a superset that contains individuals and
companies and allocates the keys. This will be the FK from the Address
table, although I would have throught that your relationships is the wrong
way round and address is a FK from companies or individual.
As far as drugs goes you may want to look and talk to snomed. I would be
surprised if you can be allergic to a route only (although understandable if
1KG suppository is one!). I think that you will need to directly link route
and ingredient if your ingredient includes a quantity. i.e. an
Drug-Ingredient-Route table which is then the FK in an interim
Allergy-Drug-Ingredient-Route. Anything you do come up with should be gone
over with a clinician to make sure all possible scenarios are covered.
John
"sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:C594FB72-0B24-4EED-9670-CF0316A39415@.microsoft.com...
> Want to model the following two items: Can anyone think of better
solutions ?
> ******************
> * Address *
> ******************
> Here we have an Address which could relate to a Company or an Individual,
but the same address will never relate to both company and indvidual. (this
is theory to keep the proble simple). In this senerio, a Company and an
Individual will have different addresses sometimes, and the same address
sometimes, and two or more Individuals could also have the same address.
> Tables
> --
> Company
> Individual
> Address
> --
> - Possible Solution A:-
> --
> Make Company and Individual Primary Keys Globally Unique and map them both
to the same foreign key, in one Address table.
>
> Company
> --
> CompanyId (GUID) --|
> |
> |
> |
> |
> Individual |
> -- |
> IndividualId (GUID) --|
> | Address
> | --
> +--> EntityId
> --
> - Possible Solution B:-
> --
> Make two address tables - store the exact same kind of data in two
different locations - ;(
> Company
> --
> CompanyId (GUID) --|
> |
> | CompanyAddress
> | --
> +--> CompanyId
> Individual |
> -- |
> IndividualId (GUID) --|
> | IndividualAddress
> | --
> +--> IndividualId
>
> *********************
> * Drug Allergys *
> *********************
> Here we will be modling drugs and their associated allergys, based upon
both Ingredient and Route (method of drug administration i.e.
Oral,IV,Suppository). In this case, I will have to check both Ingredient
and Route OR Ingredient ONLY OR Route ONLY to see if these has been a drug
Allergy (in the real world many many items and combinations there of).
> Tables
> --
> Drug
> Ingredient
> Route
> Allergy
>
> --
> - Possible Solution A:-
> --
> Place all drug descriptors (Ingredient, Route etc) in the Allergy table as
foreign keys.
> Drug
> --
> DrugId --+
> | IngredientMap
> | -- Ingredient
> | IngredientMapId --
> | IngredientId <-- IngredientId --+
> +->DrugId Ingredient |
> | | Allergy
> | RouteMap | --
> | -- Route | AllergyId
> | RouteMapId -- +-> IngredientId
> | RouteId <-- RouteId --> RouteId
> +->DrugId Route
>
> This problem differs from the Address problem, in that I can not create
unique keys, because I need to look at all "drug descriptors"
(Ingredient,Route etc.) at the same time, and therefore mappling all "drug
descriptors to one foreign key does not seem to be convienient.
>
> In this senerio, I have to check all columns in Allergy (WHERE clause),
ALL THE TIME, in order to get the correct results. For Example:
> SELECT AllergyId
> FROM Allergy
> WHERE IngredientId IN (
> SELECT IngredientId
> FROM IngredientMap
> WHERE DrugId = X
> ) AND
> RouteId IN (
> SELECT Route
> FROM RouteMap
> WHERE DrugId = X
> )
>|||Great idea for Company/Individual/Address - Thanks John.
The problem that I am having with the Drug Interactions is that there can be
any combination of items which could result in an allergy. I will look at
what it takes to design per your suggestion - thanks for the ideas.
Michael D.|||Hi
What I am not clear about is if you have really need to have say someone is
allergic to 250mg of Drug A but someone is not allergic to 500mg of Drug A
(and vice-versa)!!!
I think in general your drug dictionary is going to be reasonably static
therefore you can build your complex relationships upon upload and index
them appropriately. Additional design thought would be needed when/if a
Pharmacist is allowed to produce their own incantation!
John
"Sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:3F34819A-ACF2-463A-8461-A50C15EFD2A2@.microsoft.com...
> Great idea for Company/Individual/Address - Thanks John.
> The problem that I am having with the Drug Interactions is that there can
be any combination of items which could result in an allergy. I will look
at what it takes to design per your suggestion - thanks for the ideas.
> Michael D.|||The patient specific items are located in client databases - (Hospitals). I
f a patient is or is not allergic to drug "X" comes form another system...
Thanks for your help, I was able to get the Super Set to work great for both
issues.
Michael D.