Advice on DB Design for PO Numbers

[cbbankz]

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.

[Peter.Greulich]

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!

[cbbankz]

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.

[crimmelcp]

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
[email protected]

[jorgecardenas1]

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

[JohnZaleski]

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.

[crimmelcp]

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
[email protected]