Modelling relationship of order header to order lines of differing types

  • Is there standard way to model the following: -

    I need two distinct types of order line, 1 relating to goods e.g. product code, quantity, price, etc, etc, the other just additional textual information.

    The user should be able to enter a mixture of order line types on one order, so that they may be retrieved in the sequence entered.

    OrderHeader

    OrderNumber = 123456

    CustomerCode = ABCDE

    Order line 1

    Product = P1234

    Quantity = 23

    Price = 12.56

    Order line 2

    Product = P34567

    Quantity = 45

    Price = 99.99

    Order line 3

    Text = Handle with care ... very heavy

    Order line 4

    Product = P44444

    Quantity = 1

    Price = 99.99

  • I've seen it done a few different ways...in Quicken for example, every item has a memo field, so an additional description can be added to each item; Also it's possible to put in an item in Quicken with a zero price, so it's a placeholder for information that really doesn't get shipped...you know item #101 Product Description="Special Instructions" /no price

    I've also seen it where depending on the item type you selected in the application, different fields are available...Products vs Ebooks vs Services vs Rentals vs Special Instructions...so 4 different tables represent the possible line items in the invoice, because they differ widely enough to need seperate attributes. some get delivered, some just get billed, some turn on some access to download...

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks, I think your second approach would be more relevant to my needs.

    How would you retrieve a sequential list of all lines on the order if they're split across multiple tables? Would you need another table to hold the collection of e.g. OrderLineRecordType and ForeignKeyToRelevantTable?

  • i would just add a datetime field with a default of getdate to each of the 4 or 5 related tables...then i'd use a view to pull the common parts together, so i could order by that datetime field...say [FK to theheader table], itemid, description,price, and datetime., all from each of the four/five tables...in the invoice details, selecting an item would bring up a panel with all the details fro the specific item time, i guess....

    so if a product item has a CreatedDate of '2010-07-01 08:23:53.047' , and a services item has it's

    CreatedDate of '2010-07-01 08:23:57.047' (four seconds later), the order of creation is maintained automatically. no seperate table just to track the order of creation.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • You only need another table if you have a many-to-many relationship. Order header to order lines is a one-to-many relationship, so you need only store the primary key of the order header in each order line (OrderNumber in your example). You can gather all the data into a single dataset with a UNION. The UNION requires that each table have the same number of fields, so you might have to artificially create some fields during the query:

    SELECT * FROM

    (SELECT LineNumber, Product, Qty, UnitPrice, NULL AS Comment

    FROM NormalOrderLine WHERE OrderNumber = @ordernumber

    UNION ALL

    SELECT LineNumber, NULL AS Product, NULL AS Qty, NULL AS UnitPrice, Comment

    FROM CommentOrderLine WHERE OrderNumber = @ordernumber) A

    ORDER BY LineNumber

    In this example, I'm using a stored LineNumber field to manage order. This kind of field would have to come from application logic. When the new line is created in the application, it would give it a line number. You could also use a datetime per the earlier suggestion. If you did, often users still like to see line numbers. You could create those using the ROW_NUMBER() function.

    You could also flatten the normal order line to a single string:

    SELECT * FROM

    (SELECT LineNumber, CAST(LineNumber AS VARCHAR) + 'x ' + Product +

    ' @ $' + CAST(UnitPrice AS VARCHAR) AS LineDetails

    FROM NormalOrderLine WHERE OrderNumber = @ordernumber

    UNION ALL

    SELECT LineNumber, Comment AS LineDetails

    FROM CommentOrderLine WHERE OrderNumber = @ordernumber) A

    ORDER BY LineNumber

    (You could format the monetary amount better, but for this example I you get the point).

    This might work well for displaying the information, but doesn't allow you to seperate the line details into its component parts.

    You could also use XML to store the mixed data.

    Just spit-balling a few ideas. Hope some of this helps.

    --J

  • Many thanks. Your suggestions are much appreciated.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply