Alpha Video Training
Results 1 to 7 of 7

Thread: Advice on DB Design for PO Numbers

  1. #1
    Member
    Real Name
    Chris B
    Join Date
    May 2010
    Posts
    36

    Default Advice on DB Design for PO Numbers

    Alpha V10.5, Web App

    Scenario:
    Company mandates that every job that requires parts has a unique PO number assigned to each vendor that parts needs to come from. No job historically has had to have more than 3 POs attached to it, so to be safe we decided to allow up to seven POs to be created.

    I created at first 7 tables named PO1 - PO7.

    Structure of each was:
    WorkID, Vendor, PO, [Partnum1, Partname1, Quan1 - through - Partnum7, Partname7, Quan7], status(dropdown), notes.

    Thinking was, it would run faster because I Have buttons labeled PO 1 - PO 7, you click one and pops up for respective PO for data entry carrying over the workID number to keep everything tied together. Even had buttons hidden until WorkID was entered on parent form to protect user creating orphaned data.

    Problem is, people in warehouse, shipping/recieving, etc want to search by PO numbers and not by job numbers to toggle if recieved, etc.

    It obviously would be easy to do this if the PO numbers was not scattered out over 7 different tables so I need to combine all of this. What structure could I use and not have some huge bloated table that would took along time to query through?

    Any advice appreciated, and thanks in advance.

  2. #2
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,649

    Default Re: Advice on DB Design for PO Numbers

    You are violatoing the basic rule of database normalization - which might be called "repeating-disaggregate data structure" (I made that term up). But I won't turn you in. I'm not exactly clear on your table structure (jobs, vendors,...?), but it sounds like you need at least 2 tables in a set:

    Jobs[1:M]PO_Nos

    That way each job can have as few or many POs as needed.

    You also need a reverse set:

    PO_Nos[1:1]Jobs

    That way, your people can search on any PO and find the job.

    Good luck!

  3. #3
    Member
    Real Name
    Chris B
    Join Date
    May 2010
    Posts
    36

    Default Re: Advice on DB Design for PO Numbers

    "repeating-disaggregate data structure", Wow sounds serious!

    Thanks for the reply, the job number was just a relational key to use when referencing from services dbf to ponumbers dbf.

    Example, I have a button hidden that only pops into view when you enter a job number, then you can click the button and it pulls the job number into the popup modal window for entering the PO number, parts, quanities, etc in that window.

    Keeping ponumbers seperate because workers query the services dbf alot during the course of a work day, but the po numbers only when ordering parts or updating if received, shipping status and the like.

    Been hammering it out, I'm sure I'm making tons of mistakes here but seems to be working well for the time being. Learning more everyday and having fun doing it.

  4. #4
    "Certified" Alphaholic
    Real Name
    Charlie Crimmel
    Join Date
    Apr 2000
    Location
    West Virginia
    Posts
    1,695

    Default Re: Advice on DB Design for PO Numbers

    Chris:
    I do this in a PO system that I wrote for a construction company.
    It runs on a server with multiple users.
    The users log in and depending on their Uer rights, they can approve POs or just enter.
    When the PO is approved, it is available to send email to the vendor.

    There is a job table that has several fields.
    jobnum c 8
    nextpo N 8
    and others

    When the user selects the button to create a new record,the user selectes the job number and the code will read the table and increments the table and assigns the PO number to the record.

    Charlie Crimmel
    crimmelcp@suddenlink.net

  5. #5
    Member
    Real Name
    Jorge Cardenas
    Join Date
    Nov 2009
    Posts
    51

    Default Re: Advice on DB Design for PO Numbers

    This is a good place to start learning more about databases http://www.geekgirls.com/databases_from_scratch_3.htm
    http://www.geekgirls.com/menu_databases.htm

    Also if you want to know more about database normalisation which explains the rules for an efficient relational database you may start here http://www.geekgirls.com/databases_from_scratch_3.htm

  6. #6
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default Re: Advice on DB Design for PO Numbers

    A purchase order record with a unique po number and a field for job number will
    do everything you want.Indexes on the unique po number and on the job number will
    insure immediate retrieval no matter how large the file.
    Your present architecture is overly complicated and should be changed.The time it takes to do this will be more than compensated by the time you will no longer be wasting figuring out a retrieval methodology for seven different files.

  7. #7
    "Certified" Alphaholic
    Real Name
    Charlie Crimmel
    Join Date
    Apr 2000
    Location
    West Virginia
    Posts
    1,695

    Default Re: Advice on DB Design for PO Numbers

    Chris:
    Did you ever get your PO system to work?

    I do this in a PO system that I wrote for a construction company.
    It runs on a server with multiple users.
    The users log in and depending on their Uer rights, they can approve POs or just enter.
    When the PO is approved, it is available to send email to the vendor.

    If you would like to see a demo of this send me a email

    Charlie Crimmel
    crimmelcp@suddenlink.net

Similar Threads

  1. Database Design Question - Pro's Advice NEEDED!
    By 2020rob in forum Database Design
    Replies: 4
    Last Post: 12-14-2008, 02:11 AM
  2. Database design - Form Design
    By Larpup in forum Alpha Five Version 8
    Replies: 2
    Last Post: 06-11-2007, 12:45 PM
  3. Weather Database Design Advice?
    By SWWeatherGuy in forum Database Design
    Replies: 1
    Last Post: 07-25-2006, 09:39 PM
  4. Backorder design advice needed
    By Pat Bremkamp in forum Alpha Five Version 5
    Replies: 4
    Last Post: 02-22-2004, 09:56 PM
  5. Missing Numbers and Numbers Out of Order
    By Cheryl Franklin in forum Alpha Five Version 4
    Replies: 2
    Last Post: 12-20-2000, 11:34 AM

Bookmarks

Posting Permissions

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