database normalization

[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

 

[Steve Schapel]

Brian,

The way I read it, your design seems to imply that there is a separate
invoice for each Step. Is that correct?

 

[tina]

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

 

[Brian]

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.

 

[Brian]

comments in-line

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 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.

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

 

[Steve Schapel]

Brian,

Yes, that's exactly what I mean. Why have you got the mfgStep field in
the tblInvoices table?

 

[Brian]

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