February 22, 2009 at 12:49 pm
I have an entity called patientshare and by following the business rules, the model works up until the point when I need to create dependencies for the entity (PatientShare).
Definitions:
PatientShare: Represents the percentage in which a particular drug in a country is prescribed to a particular population.
Example of Data Un-normalized
Tylenol | USA | Severe Headache (pop) | 25% | 2008
Advil | USA | Severe Headache | 75% | 2008
so in 2008 75% of people with severe headache took Advil. Now this is a forecasting tool, so if a new drug comes out that is competitive the users want to build a dependency model saying how much share it will steel from the above drugs.
Lets begin by showing you how I build up the normalized patient share entity.
1)We have a drug that is mapped to a country which is mapped to a population. Thus, the final mapping table looks like this
DrugCountryPopulationID (PK)
DrugCountryID (FK) (from a drug/country mapping table)
PopulationID (FK)
In the PatientShare Table we use the DrugCountryPopulationID and a TimelineDateID to allow users to add PatientShare values for each Drug/Country/Population at a given date.
Thus the result is
--------------------
Table: PatientShare
--------------------
PatientShareID (PK)
DrugCountryPopulationID (FK)
TimelineDateID (FK)
PatientShareValue
So far so good, the above model is normalized and works. Now this is my problem,
Our requirements call for the need to create "dependency between a record in the patient-share table. This dependency holds information saying how much share can a drug take from another drug (re-calculation happens in the application, but we need to store the relationships)
The business rules are :
1) A patient share record can only take share from another record if it is in the same Country and Population and TimelineDateID
2) A record cannot take share from itself.
3) A patient share record can take share away from more then one record as long as it complies with #1.
4) A patient share record cannot take share away from a record if that record is taking share away from any other records and that same date / country / population / drug combination.
If someone can come up with a way to build a schema based on the above business rules I would love to see it.
Here were my 2 options:
Option #1
----------------------------------
Table: PatientShareDependency
--------------------------------
PatientShareDependency (PK)
ParentPatientShareID FK to PatientShareID) This is the record that will take share away from the other record
ChildPatientShareID (FK to PatientShareID)This is the record that will lose share away from the parent record
ShareTaken
*This works in that it holds the dependency information and it is related to the patient-share table. However, it is impossible to enforce the business rules. I can easily add in a record that breaks the business rules.
Option #2: I can break out the Drug/Country/Population/TimelineDate
option #2
----------------------------------
Table: PatientShareDependency
--------------------------------
PatientShareDependencyID
ParentDrug
ChildDrug
Country
Population
TimelineDate
ShareTaken
*Now I can enforce those business rules with constraints, however it really doesn't join symbiotically with the PatientShare table. I can add 2 additional fields like in option #1 (ParentPatientShareID , ChildPatientShareID) but now it becomes almost redundant in that the patient share table indirectly contains the Drug/Country/Population fields.
Anyone have a good solution ?
March 2, 2009 at 2:08 am
I really wouldn't address this in the model. Instead... if I were doing this then I generate these business rules with dml triggers.
"Who then will explain the explanation? Who then will explain the explanation?" Lord Byron
March 2, 2009 at 2:19 am
or stored procs if u r using them. You have better error handling in stored procs.
"Keep Trying"
March 3, 2009 at 11:47 am
I guess it is a design issue where to put your logic, and this may not be good advice on a database forum - but I like to keep the database constraints and stored procedures as simple as possible.
If you're not yourself highly competent in resolving issues like this in the database realm (or have other employees that is) - it would probably be easier both to design and maintain logic like this in your application.
Thorbjorn Kvam,
Project manager and DBA (design) at Payex (http://www.payex.com)
March 4, 2009 at 12:23 am
On the other hand you want to keep your table design as simple as possible so as to avoid writing complex queries to do even the simple things.
Stored procs can be used for validating data before inserting it into the db.
"Keep Trying"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply