Better Modeling Solution ? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Better Modeling Solution ?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-24 : 10:24:39

Michael writes "I want to model the following two items, and am looking for a better solution to both.

******************
* 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)

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
		   )

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-05-24 : 11:46:03

I just don't get it......do you need an amnesia drug?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35158&SearchTerms=address,company,individual

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources