February 9, 2009 at 10:54 am
I am building a forecast platform and I have one table to hold multiple adjustment types for a forecast. This schema looks like this,
ForecastAdjustmentID (PK)
ForecastAdjustmentTypeID
DrugID
AdjustmentValue (I Made this a decimal to hold integers as well)
As you can see we have multiple AdjustmentTypes (they are: Compliance, Treated Days and PatientShare)
Now the problem here is that with only the PatientShare, we can have dependencies between drugs, so a drug can lose share to another drug.
My question is this, should I break out this adjustment into its own table? Should I break out all adjustments to its own table?
February 9, 2009 at 11:11 am
I'm not sure the question can be answered with the data given, because the business-case of what your adjustment does isn't given.
Will the columns given contain all the necessary data to make reports, etc., from it make sense? Or will it require further tables or further columns to answer those kind of needs? If it'll do what's needed with this design, then use it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 12:04 pm
Ok, The adjustments are used to calculate a final forecast price of a drug.
Formula is something like
DrugPrice * TreatedDays * Compliance * PatientShare = ForecastSalesPrice for a givendrug
Definitions of ajustments
TreatedDays = The #of days in a year in which a member of a given population will be treated with a particular drug.
Compliance = The percentage of a population agreeing to and undergoing some part of treatment which involves consuming a particular drug.
PatientShare = Represents the percentage in which a particular drug is prescribed to a particular population.
I consider TreatedDays, Compliance and PatientShare as adjustments.
However, with the patientshare adjustment, users can define dependent relationships between drugs (drugs that take patientshare away from another drug).
My initial approach was to put all adjustments in one table (as you saw). Then I would create a PatientShareRelationship table for holding the relationships the user makes for Drugs of PatientShare.
Thus, it would look something like this
PSRelationshipID (PK)
ParentForecastAdjustmentID (FK to ForecastAdjustmentID )
ChildForecastAdjustmentID (FK to ForecastAdjustmentID )
PercentTaken
My question is this, can I restrict the ability to add ForecastAdjustmentID where the type is not = to patientshare for this table, so no one can add relationships between of any other adjustments?
Part of me feels since PatientShare's have this dependence functionality it should be its own entity, but need some guidance.
February 10, 2009 at 7:53 am
Will a drug have more than one price? How about more than one compliance value or more than one "treated days" value?
Perhaps the correct solution would be to do those calculations on groups of drugs, with some groups only having one member. Instead of "DrugID", the table would have "GroupID", and a table called Groups would have "GroupID" and "DrugID" and perhaps a percentage value to indicate which one gets which share of the adjustment. Might that make more sense?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply