Re: How would you approach this?

"John Gilson" <jag_at_acm.org> wrote in message news:<SMdnb.49419$pT1.25586_at_twister.nyc.rr.com>...
> "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
> -- other customer details
> )
>
> 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,
> quantity INT NOT NULL CHECK (quantity > 0),
> -- maintains constraint of ship date being >= order date
> order_ship_by_date TIMESTAMP NOT NULL
> CHECK (order_ship_by_date >=
> (SELECT order_placed_date
> FROM Orders AS O
> WHERE O.order_id = order_id)),
> FOREIGN KEY (product_number, product_size, product_color)
> REFERENCES Products (product_number, product_size, product_color),
> PRIMARY KEY (order_id, product_number, product_size, product_color)
> )
>
> 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,
> -- maintains constraint of ship date being between now and the latest
> -- date this order item can be shipped
> ship_date TIMESTAMP 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)),
> -- maintains constraint that quantity shipped is > 0 and <= the amount
> -- of this order item outstanding, i.e., that hasn't yet been shipped
> quantity INT NOT NULL
> 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)),
> FOREIGN KEY (order_id, product_number, product_size, product_color)
> REFERENCES OrderItems (order_id, product_number, product_size, product_color),
> UNIQUE (order_id, product_number, product_size, product_color, ship_date)
> )
>
> 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

Just curious, but which of the unknown and unspecified business requirements caused you to choose a 4-part composite primary key? Your solution is not particularly relational as it is an arbitrary SQL solution. Received on Tue Oct 28 2003 - 02:46:07 CET