How would you approach this?

Hi

I posted this hypothetical example previously deep within a thread discussing, among other things, the differences in approach to tackling real world issues in the Pick/MV and traditional relational models, and have not seen any attempts made to solve it based on existing relational theory - or anything else. I am interested in the different approaches people might take. I decided to start a new thread in the hope that might possibly bring it to the attention of someone willing to attempt a solution who might otherwise have missed my original post.

The responses I have had are mainly to do with tables and data related to the invoice but not specifically defined - things like "what about the customer file?". I'm perfectly happy for you to cater for these additional tables and relationships in any way you see fit, rather than enlarge the specification of the problem to cater for them. If I was to expand the specification of the problem to cater for Customers then someone could possibly require expansion to include details relating to the Customer - and so it could go on. I see those as extraneous to the stated requirement. Add whatever you feel is appropriate for meeting the integrity contraint requirements etc..

The example includes a typical approach that might be taken to meet the requirements using Pick/MV. I've left it in to further illustrate the nature of the problem. I hope it doesn't add confusion instead.

Regards
Mike.

> > >
> > > Example:
> > >
> > > We have a sales ledger requirement.
> > >
> > > We need to be able to record, against each invoice:
> > >
> > > * The Products ordered; (The product codes are composite keys
> > > comprising a generic Product Number concatenated with a Size code and
> > > a Colour code - only we don't know whether they are numeric or
> > > alphabetic or alphanumeric, or how many characters long they are. All
> > > we know for sure is that they must exist on the Product file, that the
> > > Product File is keyed on Product Number, and that there are lists of
> > > allowable Sizes and Colours for each Product item)
> > > * The Quantity of each Product ordered; (Must be a whole number of
> > > units greater than 0)
> > > * The Date on which each Product ordered is required to be despatched
> > > by; (we know they want to be able to despatch products in partial
> > > shipments as and when they're available when the truck leaves the
> > > depot on its delivery rounds)
> > > * The Quantity of each partial shipment;
> > > * The Date of each partial shipment.
> > >
> > > There are other requirements - but they are not known/defined at this
> > > stage. We were lucky to get this much detail. The CEO for the company
> > > we're writing this application for is a very hard man to pin down. He
> > > wants it now but he doesn't know what it is exactly. We had a five
> > > minute chat with him and, based on that, we come up with:-
> > >
> > > Pick file layout description:
> > > File: INVOICES
> > > ID: Invoice Number
> > > 001: Product codes (multivalued)
> > > 002: Quantity ordered (multivalued)
> > > 003: Requested Ship-by Dates (multivalued)
> > > 004: Quantity despatched - for each partial shipment (subvalued)
> > > 005: Despatched Date for each partial shipment (subvalued)
> > >
> >
>
> We want to store and retrieve data relating to invoices. There are 5
> things we know about them. There can be an infinite number of Product
> codes, each with a single numeric quantity ordered and a single
> ship-by date. For each related (product, quantity & ship-by date)
> there can be an infinite number of partial shipments comprising a
> (single numeric quantity despatched and a despatch date).
>
> There might also be no shipment for a product on order btw.
>
> >
> > You mean you can't have a record type
> > type InvoiceLine = record ProductCode: int , QuantityOrdered: float,
> > QuantityDispatched: float, RequestDeadline: date
> >
>
> I don't know. In your equivalent database design you're free to do
> whatever you like - so long as you give the customer what they need.
>
> > And have a single multivalued column with those records ? So how does
> > the DBMS *enforce* the needed integrity constraints ?
> >
>
> Up to you.
>
> > > We create the file and write a program to allow them to start using
> > > it. They spend the morning entering data they had recorded on various
> > > bits of paper.
> > >
 

Clarification: add whatever integrity constraints you deem appropriate. Incidentally, these would take the form of validation of the input in a typical Pick implementation.  

> > > Later that day we look at some of the data. Here's what it looks
> > > like:-
> > >
> > > Actual Pick file contents:
> > > INVOICES
> > > ID: 12345
> > > 001: ABC*123*RED]ABC*456*RED]DEF*123*BLUE]DEF*789*WHITE
> > > 002: 100]400]200]300
> > > 003: 13085]13090]13090]13087
> > > 004: 50\50]230]200]100\120
> > > 005: 13079\13080]13080]13079]13078\13080
> > >
> >
> Each line (001-005) is what we call an 'attribute', See the layout
> described above.
>

The word 'attribute' has a special meaning in Pick and is not to be confused with the definition of an attribute in relational theory.

> The 'ABC' and 'DEF' in attribute 1 are Product Numbers. Funny looking
> numbers aren't they. Hang on - I'll ask why they're called numbers...
>
> ...The woman in the office says they used to be numbers but they
> changed some of them to letters a while ago. They still refer to them
> as numbers though. Some of the products are numbers, some are letters
> and some have hyphens and things in them.
>

Note that the product numbers can contain 0-9, A-Z, '-' ,'/' & '#' characters.  

> The '123', '456' & '789' are Sizes.
>
> The '*' is just an asterisk. Could be anything really - so long as
> it's not something that's ever going to be in any of the values it's
> delimiting.
>
> Oh yes! The ']' & '\' characters? The ']' represents a char(253) which
> is a Pick system delimiter called a 'value mark'. They separate each
> of the multiple values within each attribute. The '\' is a char(252)
> and we call that a 'subvalue mark' - to separate the multiple
> sub-values within each 'multivalue'. Don't worry too much about them.
> They don't mean anything outside of Pick.
>
> >
> > > The boss now wants a printout of this particular invoice.
> > >
> >

The Pick approach to enquiring on the data:  

> > > We create some dictionary items:
> > >
> > > ED DICT INVOICES Product Size Colour OrderQty ReqShipDate ShipQty
> > > ActShipDate
> > >
> > > Product
> > > 001 S
> > > 002 1
> > > 008 G*1
> > > 009 L
> > > 010 7
> > >
> > > Size
> > > 001 S
> > > 002 1
> > > 008 G1*1
> > > 009 L
> > > 010 4
> > >
> > > Colour
> > > 001 S
> > > 002 1
> > > 008 G2*1
> > > 009 L
> > > 010 6
> > >
> > > OrderQty
> > > 001 S
> > > 002 2
> > > 003 Quantity]Ordered
> > > 009 R
> > > 010 8
> > >
> > > ReqShipDate
> > > 001 S
> > > 002 3
> > > 003 Ship By
> > > 007 D
> > > 009 R
> > > 010 11
> > >
> > > ShipQty
> > > 001 S
> > > 002 4
> > > 003 Quantity]Despatched
> > > 009 R
> > > 010 10
> > >
> > > ActShipDate
> > > 001 S
> > > 002 5
> > > 003 Despatched On
> > > 007 D
> > > 009 R
> > > 010 13
> > >
> >
> >
> > > ...and produce a listing by entering:
> > >
> > > :list invoices '12345' product size colour total orderqty reqshipdate
> > > total shipqty actshipdate
> > >
 

Page   1     invoices                                    18:12:14 23

invoices.. product size colour Quantity Ship By.... Quantity.. Despatched On

                               Ordered              Despatched

12345      ABC     123  RED         100 28 Oct 2003         50   22
Oct 2003
                                                            50   23
Oct 2003
           ABC     456  RED         400 02 Nov 2003        230   23
Oct 2003
           DEF     123  BLUE        200 02 Nov 2003        200   22
Oct 2003
           DEF     789  WHITE       300 30 Oct 2003        100   21
Oct 2003
                                                            20   23
Oct 2003
***                                1000                    650