[thelist] inventory database design question

Richard Bennett richard.bennett at skynet.be
Sun Mar 9 06:39:01 CST 2003


Hi,
I'm working on an inventory application, and  have a question about the
database design.

I have an "articles" table.
An article could be, for instance, a modem.
The same modems all have different serial numbers, and could come from a
different suppliers, maybe at a different price, yet they are the same
article.

On the other hand some articles, like "phone cable" doesn't have a serial
number.

When an article leaves stock, we'd like to be able to say:
sold:
modem with serial nr111
3 x phone cable

So I'd like the phone-cable to be booked on a first-in-first-out system, and
the modem to be selected by serial-number.

Am I right to combine these two in the same tables?
I am currently going with two tables, like this:


Articles:
artID
type
make
model
description
priceOut
termsOut

Articles Detail:
artDetailID
artID
supplierID
priceIn
serial
termsIn
dateIn
dateOut

and linking Articles to Articles Detail by artID in a one to many relation.
Would that be a robust setup, or am I overlooking something?

Thanks,
Richard.












More information about the thelist mailing list