Business requirement making design tricky

[Corey-g via AccessMonster.com]

Hi All,

I have a problem that I'm not sure how to handle.

In an order entry system, one would normally have a product and a quantity
for each item ordered. One issue here is that I need to serialize certain
products (serial number, asset tag, etc) but not all, and also be able to
track an item all the way through the process (from when the order came in to
when it was complete, what items were used to fill, etc...). This sounds
normal, except that some items are stocked (so the item 'procured' won't be
used to fill the order). And I also need to be able to determine what
product went on what order, and what order it originated from (hope that
makes sense).

So if someone orders 3 monitors, i need to be able to input the item and
quantity, then another user would see that these were ordered and actually
'procure' them. Another user would see that he had an order he could fill
(from stock) and would fill the order. When the items 'procured' arrive, the
user would need to input each items serial number and name, and place into
stock to be used for another order.

I think I've confused myself in trying to type out the problem

So what I'm thinking is to have a routine that would insert the ordered item
as many times as there is quantity. Then I could have the serialization info
stored in the ordered_item table (It's currently a seperate table because not
all products need this info), as well as an 'order_filled' field as well for
when the item goes out...

I'd be happy to answer any questions about my issue, as I'm not sure typing
more info without direction would be opf any use...

Thanks in advance,

Corey

 

[Guest]

Overall - I don't find your description possible to follow - on the otherhand
I do understand that generically in some Forms/Tables people want to put the
summary i.e. Quantity = 3 ....and in other Forms/Tables each item must be
listed separately with a unique serial number.

This situation is not uncommon.

You will have a Table of all orders - each with a unique order ID and
summary information. And you will have another Table of Details - that will
list the Order ID and the specific details such as Serial Number. Each will
be cross referenced to it's unique Order ID.

You may even have multiple tables of Details for different type parts.

Overall this is the data 'normalization' you have to sit down and figure out
how the info is broken into its logical elements, organized into tables and
cross referenced.

 

[Corey-g via AccessMonster.com]

Thanks for the response NTC.

I had figured that this wouldn't be uncommon - I just wasn't sure that I was
designing it in the most apropriate manner. So in reading your response, it
seems that having a routine that would insert the ordered item as many times
as there is quantity is the usual method.

Thanks,

Corey

 

[Guest]

well ...thinking in terms of "routines that insert" is something you need to
get away from...a database is passive. one enters data into the db. the db
does not insert...

you will have a table that has summary data - each record with a unique ID

it will be cross referenced by that unique ID to another table that will
have details

i.e. Table 1 : Record 1 : TVs Quantity 5 : PO 123

i.e. Table 2 : Record 1 : TV Serial Number AAA PO 123
Record 2 : TV Serial Number AAB PO 123
Record 3: TV Serial Number AAC PO 123
Record 4 : TV Serial Number AAD PO 123
Record 5 : TV Serial Number AAE PO 123

If you design it well - everything is cross referenced and no info is
repeated except for the cross referencing field.

Obviously if you had always small orders then you would not need two
tables....or if you did not have serial numbers - such as candy - you would
not need two tables... but when you must have summarized info and detailed
info then you go into multiple tables.

If instead of TV it is a specific model number....that model number might
have its own table with a parts list...so it can go on and on and become
pretty hairy...try to keep it simple.