July 1, 2010 at 4:45 am
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
July 1, 2010 at 6:03 am
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
July 1, 2010 at 6:11 am
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?
July 1, 2010 at 6:26 am
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
July 1, 2010 at 6:41 am
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
July 1, 2010 at 6:59 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy