Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Optimum design (Acc 2k2 (as 2k, DAO))

    Hi All,
    I'm trying tweak a PartsList design and have run myself i circles. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    The rules given to me are:
    1 sale price per PartsStockNumber.
    1 PartsStockNumber for the "same" part. i.e. a 20 gig HDD is a 20 gig HDD, no matter the manufacturer or vendor, although they will have different PartsID's on account of different purchase costs, manufacturers, makes/models, etc. (Major descrepancies between parts warrant a different or new PartsStockNumber, however, and that will not be any of my decision.)
    Need to keep records of the purchase price of individual parts (or a group of the same parts on the same PartsReceived Invoice).
    There are other rules, but none which (seem to) apply to this situation.

    I use a function which calculates the 'realtime' QuantityOnHand by grabbing the quantity and date from the last HardCountInventory, add receivables and/or parts returned and subtract parts sold, all since that date.
    Questions (re: the attached .jpg file):
    Where do I place the PartsStockNumber in order to do what is required by 'the Rules' and what other field data should be 'tabled' with it?
    Should I place the VendorID as an fk into tblOrders and tblReceiving instead of where it now resides, in tblPartsList?
    This 'seems' rather busy, like I may have too many details tables or that I don't have items in their proper place. Should I cut this down? or contrarily is it not enough?

    Note: the VendorContact will be moved out of tblVendor as soon as I am finished with this design phase.

    This is like my dozenth design, and I do believe it is very close to what I started with, each with its own advantages and disadvantages (in my eyes anyway).
    Any and all comments, suggestions, tweaks and/or questions welcomed.
    Thank you.

    Happy Holidays,

    gdr
    Attached Images Attached Images
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Optimum design (Acc 2k2 (as 2k, DAO))

    Hi Gary,
    It seems to me you have a fundamental problem with the rules, since multiple vendors can provide the same part. For that reason you can't have a link to the vendor table as you've shown it, since you have a primary key on the PartsID. It appears you would need a one-to-many table listing the different vendor part numbers that make up the PartsStockNumber record, and the vendor table should hang off that new table. Hope I've understood your problem correctly.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimum design (Acc 2k2 (as 2k, DAO))

    Hi Wendell,
    Please pardon my extreme tardiness on replying. I have had quite a few interruptions as in vacation days (left the danged machine alone!! <img src=/S/flee.gif border=0 alt=flee width=25 height=25> , sick days (thank goodness they weren't concurrent with vacation) as well as the catch-up days and some other concerns as well. I'm very sorry to be so late. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    I knew tblVendor was in the wrong place and would have placed a join table between tblPartsList and tblVendor as I had done with the Orders and Receiving tables. Would the attached image be a viable solution? Or should I use that join table with VendorID and PartsStockNumber as well as PartsID as foreign keys in it? I'm sorry, but sometimes I feel pretty dunderheaded and get lost in trees and forests, I'm afraid. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Thanks for your help.

    gdr
    Attached Images Attached Images
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Optimum design (Acc 2k2 (as 2k, DAO))

    It looks to me like your design should work. In fact, your table called tblPartsDetails is essentially a linking table with some information pertinent to the link. In design situations like this, there are often multiple correct answers, with some being better than others, so it can be a challenge to figure out which solution to use.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimum design (Acc 2k2 (as 2k, DAO))

    Hi Wendell,
    Thank you for your time and patience. This is what I'll go with.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •