Re: How would you approach this?

"Mike Preece" <michael_at_preece.net> wrote in message news:1b0b566c.0310260501.72866c5b_at_posting.google.com...
> 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
> Oct 2003
>
> 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

Here's a relational solution written in Standard SQL (SQL/92). If expressing the solution in the DBMS I work with, SQL Server 2000, the major change would be the need to specify constraints that require subqueries with triggers instead as Transact-SQL doesn't yet implement that part of the Standard. Triggers in T-SQL are procedural and proprietary objects, as opposed to the preferable declarative and standard ones, but they do allow an in-database means of maintaining the required integrity.

CREATE TABLE Customers
(

customer_id INT NOT NULL PRIMARY KEY,
customer_first_name VARCHAR(20) NOT NULL,
customer_last_name VARCHAR(20) NOT NULL

CREATE TABLE ProductSizes
(
product_size INT NOT NULL PRIMARY KEY
)

CREATE TABLE ProductColors
(
product_color VARCHAR(10) NOT NULL PRIMARY KEY

                       CHECK (CHAR_LENGTH(product_color) <> 0)

CREATE TABLE Products
(
product_number VARCHAR(10) NOT NULL, -- Misnomer but maintained as

• company-specific jargon product_size INT NOT NULL REFERENCES ProductSizes (product_size), product_color VARCHAR(10) NOT NULL REFERENCES ProductColors (product_color), PRIMARY KEY (product_number, product_size, product_color) )

CREATE TABLE Orders
(
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES Customers (customer_id), order_placed_date TIMESTAMP NOT NULL

                               CHECK (order_placed_date <= CURRENT_TIMESTAMP)

CREATE TABLE OrderItems
(
order_id INT NOT NULL REFERENCES Orders (order_id),

product_number VARCHAR(10) NOT NULL,
product_size INT NOT NULL,
product_color VARCHAR(10) NOT NULL,

                                 CHECK (order_ship_by_date >=
                                                 (SELECT order_placed_date
                                                  FROM Orders AS O
                                                  WHERE O.order_id = order_id)),

CREATE TABLE OrderShipments
(
shipment_id INT NOT NULL PRIMARY KEY,
order_id INT NOT NULL,

product_number VARCHAR(10) NOT NULL,
product_size INT NOT NULL,
product_color VARCHAR(10) NOT NULL,

                CHECK (ship_date BETWEEN
                               CURRENT_TIMESTAMP AND
                               (SELECT order_ship_by_date
                                FROM OrderItems AS I
                                WHERE I.order_id = order_id AND
                                               I.product_number = product_number AND
                                               I.product_size = product_size AND
                                               I.product_color = product_color)),

             CHECK (quantity > 0 AND
                             quantity <=
                             (SELECT I.quantity -
                                             COALESCE(SUM(S.quantity), 0)
                              FROM OrderItems AS I
                                          LEFT OUTER JOIN
                                          Shipments AS S
                                          ON I.order_id = order_id AND
                                                I.product_number = product_number AND
                                                I.product_size = product_size AND
                                                I.product_color = product_color AND
                                                S.order_id = order_id AND
                                                S.product_number = product_number AND
                                                S.product_size = product_size AND
                                                S.product_color = product_color AND
                                                S.ship_date < ship_date
                              GROUP BY I.quantity)),

Data on all shipments can be found with the following query:

SELECT O.customer_id,

               O.order_placed_date,
               O.order_id,
               I.product_number,
               I.product_size,
               I.product_color,
               I.quantity AS quantity_ordered,
               I.order_ship_by_date,
               S.quantity AS quantity_shipped,
               S.ship_date
FROM OrderShipments AS S
            INNER JOIN
            OrderItems AS I
            ON S.order_id = I.order_id AND
                  S.product_number = I.product_number AND
                  S.product_size = I.product_size AND
                  S.product_color = I.product_color
            INNER JOIN
            Orders AS O
            ON O.order_id = I.order_id
ORDER BY O.customer_id, O.order_placed_date, O.order_id,
                     I.product_number, I.product_size, I.product_color,
                     S.ship_date

Regards,
jag Received on Mon Oct 27 2003 - 19:49:22 CET