Re: normalization question

If you want the same issue in a real world situation, here is one I ran into a few years ago:
1) There are a set of consumers who receive sales reports. Each consumer can choose to receive those reports in any number of media formats (excel sheets, website, email, etc.). 2) Sales reports are generated on a periodic basis. They contain a set of sales that is determined by the consumer/media format combination, along with some temporal data (i.e. it’s not simply a matter of saying report X is all sales matching the consumer/media format combination Y from January 1 to February 31, since the data that determine if sale matches or not can change). 3) A sale that is in a sales report cannot appear on any future sales reports for that consumer/media format combination, as this would be considered double reporting.
The question is how to model this to properly keep track of which sales were reported when and enforce rule #3

And again, we have the two choices I’ve come up with: 1)
  Report entity, with a unique key of ConsumerID, MediaFormatID, and Date
  ReportSales entity with a unique key of ReportID and SaleID Which does not enforce rule #3

2) ReportSet entity with a unique key of ConsumerID and MediaFormatID Report entity with a unique key of ReportSetID and Date ReportSales entity with a unique key of ReportSetID and SaleID and contains ReportID
Which could lead to data inconsistencies.

I’ve run into this situations a number of times and have not yet found a satisfactory answer. Any ideas?

On Apr 23, 3:51 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> paul c wrote:
> > myotheraccount wrote:
> > ...
>
> >> 3) Items can only be sent once from each vendor to each customer
> >> ...
>
> > Does this mean a customer can't buy tomato soup twice from the same vendor?
>
> It's not really customers, boxes and items. See the part where the OP
> said "the actual information I am modeling is confidential".
>
> That means, if he told us what it really is, his TA would have to kill us.
Received on Thu Apr 23 2009 - 23:33:58 CEST