Orders & Products

[mastromb]

To add to my list of road blocks that I am facing with my database design, I have an issue with my order tables and my products tables.

First off I have my tblOrders linked to my tblOrderDetails by the Primary Key OrderID. But when it comes to the Products that populate the tblOrderDetails I don't know what to do because there are times that I have special order products that may only be one time use or the next time I purchase it the specs are different so I need again create a new product entry. To get around this I first designed the tblOrderDetails to include the basic product information such as Name, Supplier, Product Category, Unit Cost, Unit Price, etc. However as I did some research on Normilization it appears that I am breaking ever rule in the book by doing this.

So I guess the real questions remains to I continue to break the normilization rules and suffer the consequences that come with that or do I force myself to create a new product forevery unique situation that comes with that?

[RuralGuy]

It depends on how much pain you want to endure. Bite the bullet and create a new product for every special case.

[mastromb]

Ok, so if I resign myself to creating a new entry for every special order product or special case how would I then handle/keep track of the unit costs and unit price of products as they change whether it be a special quote for a job/order or just a change in the unit cost do to a price increase based on an effective date or depletion of the inventory quantity at a certain purchase price?

[RuralGuy]

The way you planned to do it originally.

[carlmdobbs]

I suggest that if the product is of no particular consequence, that you make a product called "Widget" that encumpasses all of the little things you don't want to individually classify. In the case of a widget you can, if you want, have a separate table that records the details of that widget that you want to retain. Example: You register the sale of a widget. You have a drop-down list or a place for entry of the price to record it. But you can have Widget1 which sells for 10 dollars, Widget2 that sells for 20 etc. so that you price the different categories of widgets to specific prices that you know will be enough to make you a profit. (Remember, widgets aren't your main income and only sold for the convenience of the customers.) The details of all widgets in a particular category are standardized and it doesn't reallly matter if you record everything with all t's crossed. Take this global approach and it may suit you just fine.
Remember one rule: You may not have to dot all your i's and cross all your t's. Just remember not to put sugar dots in all your teas with your eyes crossed. Get the message? Use practical sense.
Another rule: You can vary the cost of each individual sale of a particular widget so long as the price you store is rounded to the nearest dollar. At the end of the day your books will be off by less than $1.00 and the difference will cancel itself over a week's time. Whatever discrepency is left over the year can be assigned to Misc. income or expense.

To add to my list of road blocks that I am facing with my database design, I have an issue with my order tables and my products tables.

First off I have my tblOrders linked to my tblOrderDetails by the Primary Key OrderID. But when it comes to the Products that populate the tblOrderDetails I don't know what to do because there are times that I have special order products that may only be one time use or the next time I purchase it the specs are different so I need again create a new product entry. To get around this I first designed the tblOrderDetails to include the basic product information such as Name, Supplier, Product Category, Unit Cost, Unit Price, etc. However as I did some research on Normilization it appears that I am breaking ever rule in the book by doing this.

So I guess the real questions remains to I continue to break the normilization rules and suffer the consequences that come with that or do I force myself to create a new product forevery unique situation that comes with that?