Design Issue - Which way is better

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

General SQL Server Forums

Database Design and Application Architecture

Design Issue - Which way is better

Author

Topic

peryan77
Starting Member

8 Posts

Posted - 2009-02-21 : 12:33:08

Here is the problem,

We have the following entities,
Drug,
Country,
Population,
Timeline,

Users will map drugs to a country and then map those to a population,
Thus you end up with

Map to Country
DrugCountryID | DrugID | CountryID

Map above to population
DrugCountryPopulationID | DrugCountryID | PopulationID

Now we have this entity where users put in assumptions called
PatientSharePercent.

In theory it should include the above mapping id (DrugCountryPopulationID) but the DBA broke it out like this

PatientShareID
DrugID
CountryID
PopulationID
TimelineDatesID
PatientShareValue

And then he said we can use the mapping table (DrugCountryPopulation) in a join to build the framework for the above table in the application(we have to cross join with the timeline as there needs to be assumptions entered for all the time dates of the timeline.

His reasoning is there is less joins when dealing with the patientshare table.

However, there is another entity we need to build from the patientshare. I call it patientshare dependency. Which is where a user can define a relationship between a patientshare record against another patient share record (drug takes share away from another at that point in time)

So My schema looks like this for that

ParentPatientShareID | ChildPatientShareID | PercentTake

However, the problem is I cannot but constrains based on the business rules that
A drug can only take share away from another drug at the same timepoint of the same country and population.

Thus, a drug in the USA in a Severe population in 2008 can only take share away from another drug in the USA in the severe population and only during 2008.

There is no way I can enforce that with the prior schema. Is this ok that the application handles this? Or should I change my dependency schema to something like

ParentDrug ChildDrug Country Population PeriodTimelineID PercentTaken

but doing so will lose the direct relationship with the patientshare table,

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-02-22 : 07:06:19

I'll be honest, I can't really follow what you're trying to do but you seem to be thinking along the right lines.
Some guidelines that might help:
1. Always model your data correctly as per the business rules. Performance can be addressed in many ways but a broken data model cannot. If you fix one thing then break another then think a little longer about the problem. There is always a way.
2. Don't enforce stuff at the application level. If your data is useful it trancends any particular application present and future.
3. Optimise later when you can measure, benachmark and improve. Don't guess.
4. Do not compromise your model when optimising (you can do all sorts of neat stuff with precalculated totals, joined sets etc )
5. Surrogate keys are sometimes nice but not always essential. In your case consider not using them all over. This may allow you to reduce the joins by having the proper key data already in your table (DrugCountry, DrugCountryPopulation seem good candidates). Just make sure you relate them back properly to avoid illegal values.
HTH

peryan77
Starting Member

8 Posts

Posted - 2009-02-22 : 14:25:16

I will try and make it simpler.

If I following the business rules, the model works up until the point when I need to create dependencies for the entity (PatientShare).

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 the the Timeline to allow users to add PatientShare values for each Drug/Country/Population at a given forecasted 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 calls for a 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)

Here is the business rules for this:
1)A patient share record can only take share from another record if it is in the same Country and Population and TimelineDateID

2)And a record cannot take share from itself.

If someone can come up with a way to build more schema to tie in the above business rules I would love to see it.

Here were my results:
----------------------------------
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: (how much share is taken)

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

I am not very experience with creating schemas so I might be missing something here?

I thought of another schema, it can enforce the business rules, but it is sort of redundant.

Here it is: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 patientshare table indirectly contains the Drug/Country/Population fields.

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-22 : 19:49:48

>> Thus, a drug in the USA in a Severe population in 2008 can only take share away from another drug in the USA in the severe population and only during 2008.

I guess this to say, that your understanding of how to track info in a db is skewed...

ever look into database normalization

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

peryan77
Starting Member

8 Posts

Posted - 2009-02-22 : 20:02:59

Hey buddy,

I understand normalization, and my tables are normalized. I guess you can't read very well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources