Design Issue - Which option to choose?

[lhomoleski]

I have an entity called "patient share" and by following the business rules, the model works up until the point when I need to create dependencies of records from this entity.

Definitions:

Patient Share: Represents the percentage in which a particular drug in a country is prescribed to a particular population.

Example of Data

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 with existing drugs, the users want to build a dependency model saying how much share it will steel from existing drugs.

Schema for PatientShare
--------------------
Table: PatientShare
--------------------
1)PatientShareID (PK)

2)DrugCountryPopulationID (FK) (Mapping of drug to country to population)

3)TimelineDateID (FK)
4)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 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 drug record can only take share from another drug if it is in the same Country and Population and Date
2) A drug cannot take share from itself.
3) A drug can take share away from more then one drug as long as it complies with #1.
4) A drug cannot take share away from a drug if that drug is taking share away from any other drugs at that same date / country / population / drug combination.

Here were my 2 options:

Option #1
----------------------------------
Table: PatientShareDependency
--------------------------------
1) PatientShareDependency (PK)
2) ParentPatientShareID FK to PatientShareID) This is the record that will take share away from the other record
3) ChildPatientShareID (FK to PatientShareID)This is the record that will lose share away from the parent record
4) 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

----------------------------------
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 Patient Share 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.

Which way should I go?

 

[JoeAtWork]

Which way should I go?

Neither - complex business rules should be handled by the business layer of the application's architecture. This is usually coded in a higher level language (C++, VB, C#, etc.) in a DLL or EXE. It's not something you should try to enforce at the database level.

Normalization is not the same as enforcing business rules. The business rules is about logic, normalization is about storing data efficiently and consistently.

 

[JoeAtWork]

I shouldn't have said "neither", I should have said # 1.

Also, you could enforce the business rules in the database, by allowing additions and edits to the table with stored procedures only. The stored procedure would enforce the business rules.

However, I prefer to do this higher up, because of things like giving readable error messages to the user. Also, it saves a round trip to the server if you do the business validation before you send the update to the server.