Results 1 to 5 of 5
  1. #1
    mastromb is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Plymouth, MN
    Posts
    37

    Orders & Products

    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?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It depends on how much pain you want to endure. Bite the bullet and create a new product for every special case.

  3. #3
    mastromb is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Plymouth, MN
    Posts
    37
    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?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The way you planned to do it originally.

  5. #5
    carlmdobbs is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2010
    Location
    Maryland
    Posts
    26

    The database is made for man, not man for the database.

    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.


    Quote Originally Posted by mastromb View Post
    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?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to sort by three sort orders
    By captgnvr in forum Access
    Replies: 4
    Last Post: 11-09-2009, 07:30 AM
  2. In need of assistance-products mated to mobile#'s
    By EisBlade in forum Database Design
    Replies: 0
    Last Post: 04-06-2006, 07:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums