Re: database design

I would say a tickbox is not sufficient since you should allow for any quantity of an item being on hand or shipped.

One approach I've seen works fine uses the following kind of structure (oversimplified because that's just an example):

    item(id, item, description)
    inventory(item_id, quantity, location)     shipment(item_id, quantity, date)

When you ship an item, you basically decrease its inventory quantity by the quantity shipped, and create a shipment record. Make that a transaction so that your db is always in a coherent state.

I'd recommend that over the one-table approach since you're goig to have other transactions influencing the content of your inventory table (receiving material, moving inventory around to different locations, ...) that have nothing to do with shipments.

I guess this is close to your 1st method below.

Cheers,

SR

the fat heffer <iamthecow_at_NO_SPAMhotmail.com> wrote in message news:965987747.21754.0.nnrp-08.9e981bb7_at_news.demon.co.uk...
> i have a question about the design of a current stocklist table in a
> database.
> which of the follwing two ways is correct or is there another way of doing
> it.
> 1st method
> have one stock list which each record havinn a shipped tick box.
> and if it has beeen shipped it is not included in a current stock query
 but
> it is still in the main stock table.
> 2nd method
> When an item is shipped move it form a current stock table to a shipped
> stock table. so that there are separate tables for current stock and
 shipped
> stock.
>
> Which of these two methods is better and which is the standard method that
> is normally used.
> or is there another way of doing this
> thanks
>
>
>
Received on Fri Aug 11 2000 - 00:00:00 CEST