Order Database Design

Question

Order Database Design

Viewing 4 posts - 16 through 18 (of 18 total)

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

cathyDePaolo SSC Eights! Points: 989 More actions · Answer 1

Ah, now I understand why the function I created in our test environment set the line numbers to 1 for each line I thought I had done something wrong and was in the process of playing with the code. Plus, you are right, I do not have a linenumber already in the table.

I won't be able to use a trigger, because I can't assume that orders will be coming in one at a time. I am pretty sure our developer did not restrict the application in this way and there are no business processes in place that would prevent multiple orders coming in simultaneously. The temp table sounds like the easiest solution.

Thank you, Brandy. You were incredibly helpful.

~Cathy

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

<<What if I order the same item twice, but manage to have each item listed seperately on the order (if possible)? >>

I've had to code for this in the past, in the fashion trade. Customer asks for 23 short black nappa jackets in various sizes, and an extra 3 in size 12, 1" shorter in the cuff. The style code is the same - there's insufficient argument to go to the rigmarole of setting up a whole new style with costings, sizing and pricing structure, so the modification is listed on the order as a line below the style (product) line.

Having been bitten, I now use:

An OrderID integer id column (used by the database) which is separate and different to the order number (used by the business)

An OrderlineID integer id column (used by the database) which is separate and different to the line number (if applicable) - which is used by the business.

The style (or product) code is treated similarly: fashion in particular are fond of using the exact same style code across years or seasons with small changes in between - but these can be distinguished within the database by the ProductID, which is an integer id column.

Your database requires that there is a way of uniquely identifying each row in each table. If business entities satisfy this requirement today, then it's a concidence, and the business could easily change things tomorrow.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For 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

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 3

I'm glad I could help you out, Cathy. And Chris actually has some good ideas (the best come from personal experience) that you might want to consider also.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 4

A lot of good, sound advice. One thing you should consider no matter which way you end up going: never, EVER allow developers to dictate database design. They tell you what information will be going into the database and the information they want to get back from the database (information -- not data). Allow them to insert information into the database through stored procedures and/or web services and allow them to get information from the database through stored procedures and/or web services. Sometimes, after they have presented a sound argument, I will let them see one or more views.

Select directly from a table? Insert directly into a table? Not on my watch!

This is not an "US vs THEM" arrangement. You are not just engaging in protecting your turf. On the flip side, you should not be making any design decisions or recommendations to the app developers. The application design should be completely free of any particular database implementation. In my free time (when I have some), I develop applications -- the front end AND the database. I use this methodology even for myself. As I lay out the UI I generally have no idea yet what the database will eventually look like. And it doesn't make any difference because, well, it just doesn't make any difference.

There should be a impenetrable layer of abstraction between the app and the database. As long as you maintain that abstraction, you should be able to do anything you want to the underlying physical data. The developers can't object to schema changes because, unless you tell them, they shouldn't even know.

Not only is this good design methodology, but you will never again find your decisions concerning table definition resting in the hands of a petulant programmer.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms