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, 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
Table
--
Compan
Individua
Addres
--
- Possible Solution A:
--
Make Company and Individual Primary Keys Globally Unique and map them both to the same foreign key, in one Address table
Compan
--
CompanyId (GUID) --
|
|
Individual
--
IndividualId (GUID) --
| Addres
| --
+--> EntityI
--
- Possible Solution B:
--
Make two address tables - store the exact same kind of data in two different locations - ;
Compan
--
CompanyId (GUID) --
| CompanyAddres
| --
+--> CompanyId
Individual
--
IndividualId (GUID) --
| IndividualAddres
| --
+--> IndividualI
********************
* 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)
Table
--
Dru
Ingredien
Rout
Allerg
--
- Possible Solution A:
--
Place all drug descriptors (Ingredient, Route etc) in the Allergy table as foreign keys
Dru
--
DrugId --
| IngredientMap
| -- Ingredien
| IngredientMapId --
| IngredientId <-- IngredientId --
+->DrugId Ingredient
| | Allerg
| RouteMap | --
| -- Route | AllergyI
| RouteMapId -- +-> IngredientI
| RouteId <-- RouteId --> RouteI
+->DrugId Rout
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 AllergyI
FROM Allerg
WHERE IngredientId IN
SELECT IngredientI
FROM IngredientMa
WHERE DrugId = ) AN
RouteId IN
SELECT Rout
FROM RouteMa
WHERE DrugId =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
> )
>|||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). If 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.