database normalization

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Ok, I thougght I had it, but now I am stuck

I am trying to track processes in a manufacturing environment. There are
several steps in the process but not every item goes through each step. I
want to track the dates each step was completed.
My db is set up like

tblCust
(PK)custID
Name (and all the other particulars)

tblInvoices
(PK)invoiceNum
custID
mfgStep (filled from a list table)
dateCompleted
NAstep (Y/N)

tblInvoiceDetails
invoiceNum
style
(other Particulars of the order)

Is this set up correctly? How can I report the dates each individual step
is completed as well as those that are still in process.

Thanks
 
Brian,

The way I read it, your design seems to imply that there is a separate
invoice for each Step. Is that correct?
 
well, if you manufacture more than one item, then presumably the customer
can order more than one item at a time, correct? so a single invoice must
contain details about each item ordered. does your customer need to know
about the steps in the manufacturing process of the item(s) s/he orders? if
not, perhaps the steps in the manufacturing process should not be connected
to invoices at all. you need to decide if Sales and Production are two
separate aspects of the business; if they are, are you tracking both? or
only one?

the first step in creating a relational design is to analyze the process -
you can't model a real-world business process with tables/relationships,
until you understand that process. beyond normalization rules, which can
seem kind of abstract, you may benefit from some study of process analysis.
Michael Hernandez wrote a textbook called Database Design for Mere Mortals,
which breaks down the job of process analysis into understandable steps;
it's been recommended often in these newsgroups.

hth
 
Each customer buys usually one product, which generates each invoice. Of
course a customer can generate multiple invoices, but each invoice indicates
a product being manufactured. This product may go through all 9 steps, or
may only need the 7 of the steps. example: Customer A comes in and buys a
desk with file drawers and book shelves. This invoice would take all 9 steps
to complete. I would like to track the dates of the order, dates costruction
started, date desk was finished, date shelves were finished, etc etc.
Customer B comes in and just buys a desk, no shelves, no drawers. Seperate
invoice number, and the desk process is the same, but there is no shelves or
drawers to track - therefore there would be no entry for those steps.
 
comments in-line

tina said:
well, if you manufacture more than one item, then presumably the customer
can order more than one item at a time, correct? so a single invoice must
contain details about each item ordered. does your customer need to know
about the steps in the manufacturing process of the item(s) s/he orders?
if
not, perhaps the steps in the manufacturing process should not be
connected
to invoices at all. you need to decide if Sales and Production are two
separate aspects of the business; if they are, are you tracking both? or
only one?
Click to expand...
The invoice is generated by the sale. One customer - potentially many
invoices. Each invoice contains a product with potentially several
configurations of essentially the same product; ie desk with 3 drawers, desk
2 file drawers, desk with shelves but no drawers, desk with shelves and
drawers, desk with drawers, shelves and a caqbinet - etc
the first step in creating a relational design is to analyze the process -
you can't model a real-world business process with tables/relationships,
until you understand that process. beyond normalization rules, which can
seem kind of abstract, you may benefit from some study of process
analysis.
Michael Hernandez wrote a textbook called Database Design for Mere
Mortals,
which breaks down the job of process analysis into understandable steps;
it's been recommended often in these newsgroups.
Click to expand...

I have heard of this book and it is on my list to get when funds become
available.
 
Brian,

Yes, that's exactly what I mean. Why have you got the mfgStep field in
the tblInvoices table?
 
Dang !! got ahead of myself on my original post
it should be

tblInvoices
(PK)invoiceNum
custID
orderDate
(Particulars of the order - style, finish, components, etc)

tblProdDates
(PK)mfgDetailID
invoiceNum
mfgStep
dateCompleted
 
Back
Top