Order Database Design
-
Hi. I have a question for all of you related to the best way to design a database that will be used to process orders. I have an Order Master table along with an Order Details table. I use OrderId in the master table as my PK and also store it in the Order Details table as the FK. My PK in the Order Details table is an identity field. In addition, I have a Line Number field, indicating the number of lines per order. Every time I've designed an order db or been exposed to an order db, line numbers were always stored.
The application developer says that the line number is redundant data and does not want to deal with the overhead required to determine the line number. I can think of a couple of ways that she can do this without it being too big of a deal. However, if she is correct, and this is redundant data, I would rather remove it from the database.
My question: Is the practice that I follow of including line numbers within the order details table a solid practice or am I just creating more work?
Thanks in advance for you help.
Cathy
-
Pardon me for this, but I am practicing to be a consultant: It depends. Since you are using an identity field in the OrderDetail table, the LineNumber column could be considered redundent. The other side of the coin, however, is that the combination of OrderNumber/LineNumber could be used as either the Primary Key (instead of the identity field) or as an Alternate Key for the table (This would be another Unique index).
You also need to look at it from the perspective of the Customer. If I order several items from your company, how do I identify each item on the order, by the product or the line item? What if I order the same item twice, but manage to have each item listed seperately on the order (if possible)? Personally, I would prefer to have the LineNumber as part of the OrderDetail table.
-
Thank you, Lynn. I did consider using a concatenated key of OrderId and LineNumber, but at the last minute changed my mind. Mostly because I was trying to follow the same practice I followed with all of the other tables. Each table as it's own PK, even where a concatenated key could have been used, so I stuck with that same principle.
If there is no specific practice that I can point to, I suppose I may need to remove the line number field from the detail table. Does anyone else have anything to add to Lynn's response? I appreciate any advice thrown my way.
Cathy
-
uhmmm excuse me lynn but if you should order the same product twice wouldn't that generate two seperate order number.
And why not just use Line number instead of the identity as primary key
-
If LineNumber is used, it has to be included with the OrderNumber to make it unique. Also, based on experience, I have worked on some systems written in Fortran and COBOL where we actually had to split the total quantity of a given item actually had to be split over 2 or more line items as the total of quantity ordered times unit price exceeded the storage capacity of the total dollar amount. We also had instances where additional quantities of a given product were added with a different discount and had to be on a seperate line item.
It all comes down to business requirements and system limitations.
-
Order(ID,CustomerID,...)
LineItem(ID,OrderID,ProductID,Quantity,...)
View: Order with LineItemCount:
SELECT o.ID, o.CustomerID, ..., Count(l.ID) AS LineItemCount
FROM Order o INNER JOIN LineItem l ON o.ID=l.OrderID
GROUP BY o.ID, o.CustomerID, ...
This way the programmer doesn't have to calculate the line item count for you, but it's readily available in the view.
-
Sergey,
Thank you for your reply. However, what we need, it a line number for each record within the order. What you have provided here just gives me the number of lines per order. In other words, we need:
OrderID Line Number SKU Quantity
123 1 1AG 5
123 2 3GB 2
123 3 6H4 1
I know I can do this in a cursor, and maybe that is what I need to do to get the job done. We have implemented Lynn's suggestion and just used a record identifier. As of right now, I don't need to output the line number but I may in the near future. Any other suggestions to get a line number other than a cursor?
Cathy
-
Some other considerations:
1. If you don't use the order line number, SOMEWHERE in the database, how can you guarantee that the printing order of packing lists, invoices, dunning notices, etc. will all print the details in the same order? Won't your programmer have to 'invent' the line numbers anyway?
2. Who is setting up the specifications for this system? Why hasn't the decision been resolved prior to now, based on the printed output (and screen displays to match)?
3. Why is a programmer dictating data base design? Is this a philosophical debate over normalization to what degree? HOW MUCH extra work is this for the programmer? Is this programmer 'young' in the field? Have 'best practices' literature been consulted? I agree that 'just because you've always done it this way' is not a good reason. 'just because it's always been a requirement' IS a good reason.
Points to the programmer for asking. Points to you for asking. More points for asking more questions - and more questions should be asked on this issue before it's put to bed.
Steve
-
Reckon most of the important points are covered here, but it's also worth pointing out that it's easy to get confused between business entities and database structure entities, and if you keep them separate from the outset, you will save yourself a lot of grief later.
e.g.
Order number (business) is very often unrelated the the pk of the orders table (database), so why not assume that it may as well always be unrelated. Have something generate the order number for you in the format that the business dictates, and leave the pk alone. Changing the app & database at a later date to accomodate a change in the order numbering scheme won't be much fun.
Business may dictate that Line number may have to be consecutive without gaps. As Steve points out, it's a reference for both supplier and customer, and also provides a means of sorting the order items at print time - the desired sort order may well be different to the order lines pk sort order. The Line number may change - moving order items up and down the list...
There are circumstances where individual order lines may be subsequently referred to, including but not restricted to invoicing and "make to order" - in which case an order lines pk makes more sense than order fk & line number.
Just a start.
Cheers
ChrisM
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Here's a thought.
Orders table contains the summary version of the order.
OrderLines table contains the individual detail lines of the order including an FK to OrderID. That way you can have your LineID PK and your OrderID PK and not mess up your "1 column per PK per table" business rule, you also get to keep your line numbers (if you want them) to order things by. And the programmer doesn't have that much more work to do. Yes, he'll have some work to do, but as others have metioned, he's going to have to work this out either way.
BTW, Cursors? As a regular, frequently accessed item in your transactional database?? Not a good idea. Cursors are fine for some things. Can't be avoided for other tasks. But I don't recommend them as a regularly accessed thing as they can kill performance. Especially when you have other methods available to resolve your problems.
Sit down with the developer and go over his concerns. Ask him why he's going to have a problem with LineIDs. Get an actual list of his concerns (documenting time!). Then give him options on how he can work around this. It might be that you have a lazy / stubborn developer who wants to do things the way he wants to do things. It might be that he's thinking this is more complicated than it actually is. You won't know unless you get his reasons written down. And treat his concerns seriously. Don't patronize him or you'll never get him to work with you.
And if you can point out to him that him doing some extra work now will save on time and bugs later on, you might be able to persuade him to come around. That's if YOU want to go with the LineID thing. You should make your own list of PROS and CONS from a database side. Then you can compare your concerns with his and, hopefully, come up with a resolution that suits everybody.
-
Steve and Chris,
Thank you so much for the food for thought. While I was trapped in the thought process of requiring a line number, I lost site of the possible business reasons behind the line number itself. This application is really just a request application. The request will eventually become an order, but the order system is developed in Access and we are not able to really interact with it. It was developed 15 years ago and changes to it that are not bug fixes are really not approved unless they are required for new business processes.
The application I am working on provides a way for our support offices who are not on the same network to submit requests. These requests are stored in a SQL Server database (the database that I developed and was asking about here) and an email goes out to someone who then manually enters the request into our order application. The order application is also our invoice system, inventory system, and shipping system.
To both of your points, the real order id and line numbers are generated in the order application. If I generate a line number in the request application and associate that with the request id, I suppose there is room for confusion when the requester tries to communicate with us about his/her order. To minimize confusion, we refer to requests as requests in all communications from the application as well as within the application itself. I think everything you have said here in this forum supports leaving the line number out of it. There is no business purpose for it in this case as far as I can see.
As for the developer. She has only been programming for the last 4 or 5 years. The group I am in is not a team oriented group and so collaboration is very difficult. Communication is also very difficult. I've been trying to get the developer to work with me, but she is resistant. I am also fairly new to this field. I've been doing systems analysis and data analysis work on and off for 5 years, with a little project management and software release management thrown in. I've also been developing databases across that time span, but not with anyone knowledgeable working with me on the development and execution. I would like to become more formal with my processes and know without a doubt that I developed a well thought out and executed database. I come to this forum to help me with that, but if you have suggestions as far as other resources go, I am all ears.
Brandie, as I am typing this response to Steve and Chris, I see you responded as well. Thank you for your input. I agree that this shouldn't be a battle of the egos. In the end, I implemented the design without a line number but more so because the developer flat out refused to do it any other way. I wish we could have discussed the issue in a more productive way, but we were not able to do that. Perhaps in a future project, it will work out better than it did in this case.
Thank you to all of you. I hope to see you out here again.
Cathy
-
Cathy,
Thank you so much for clarifying that this is a 'pre-order' application. As I was reviewing Brandie's comments, I was thinking in terms of order fulfillment - partial ships, etc.
An older, business related book that I would recommend for both you and your developer to read: "Games Mother Never Taught You" - I have no idea if it's still in print or not. I think I checked it out of the library, back in the 80s. You will find its lessons useful in the business world. I was an asthmatic as a child and never played on team sports. Read the book for the rest of the relevant details.
I have found this a wonderful and very responsive forum, as long as you are willing, as the initiator of the post, to provide enough information to let people help you. We've ALL felt the pain, at one time or another.
Steve
-
IMHO, the line number is tramp data and not necessary from a DB design perspective. However if it's important to the customer to have the order the items were placed in the basket faithfully represented in the data then it becomes a business requirement. You can establish a primary key for the OrderDetail table with the OrderID and ProductNumber.
There are a number of methods that don't involve using a cursor to extablish line numbers such as temp tables or table variables with an identity column. If items are added one at a time you could create a function or a default such as:
create function dbo.OrderLineNumber(@OrderId int) returns smallint as begin return isnull((select max(LineNumber) + 1 from dbo.OrderDetail where OrderId = @OrderId), 1) end
--Paul Hunter
-
Thank you, Paul. I never thought of creating a function. I think I'll try it out as a test, even though we aren't implementing it in this database.
One question regarding the table. If I created a temp table or variable table, how would that help with establishing the line number? Would I have the identity column generate the numbers, update the detail table with the line numbers, then delete the records from the temp/variable table, and start all over for the next order so that the line numbers continually generate 1-? for each order.
-
Cathy,
There are a couple of things you should note about Paul's comment. First, he suggests using a combo primary key for your OrderDetails table which you said you wanted to avoid. Second, his function (if I am reading it correctly) actually assumes there is a LineNumber column in your table and does essentially what an Identity field column would do. That is, it simply adds +1 to the last entered LineNumber and if there are no LineNumbers entered, it will simply return 1 for each single record.
You can test it with the below code:
Create Table OrderDetails (OrderID int, LineNumber smallInt)
Insert into OrderDetails (OrderID)
(Select 999
UNION ALL
Select 888
UNION ALL
Select 777
UNION ALL
Select 666)
create function dbo.OrderLineNumber(@OrderId int) returns smallint as
begin
return isnull((select max(LineNumber) + 1 from dbo.OrderDetails where OrderID = @OrderId), 1)
end
Select Distinct OrderID, dbo.OrderLineNumber(999)
from OrderDetails
And even if you enter multiple values of the same value (999 for example) and remove the DISTINCT keyword from the Select statement, it still won't give you individual line numbers. It'll print a 1 next to each and every single record.
I'm not saying you should ignore his comment about implementing a function. That's actually a good idea. But when people suggest code to you, you should always test it against your design to make sure it works for your needs. I'm sure you can rewrite Paul's function to work with what you need, also.
For using variables / temp tables, you could read in the individual order in a temp table that has an identity field for LineNumber (this way the identity gets reset every time a new order comes in), then INSERT the records into your OrderDetails table with the LineNumber. The Pros, you have auto-line numbering without fancy functions or cursors. The Cons, LineNumber on the OrderDetails table can't be (in itself) an Identity, Unique or Primary Key column because it will repeat itself.
Other options include combo-Primary Keys, using OrderID as a foreign key in your OrderDetails table and then looping with a WHILE statement to count lines where OrderID = @OrderID. Triggers might work, using the INSERTED table as a place to count detail lines, assuming you're only inserting one Order at a time.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply