CODEWITHSUNDEEP
mysqlinventory-management
r2b2
r2b2

Reputation: 1275

Stocks management for warehouse

I am trying to create a stock management system for a warehouse which has various items. I'm currently stuck with thinking how my tables should look like. I am designing it based on the delivery receipts my boss handed to me. Here's what some the delivery receipts contain:

Delivery receipt# :1
Quantity: 60 pcs 
Item description : 
    T SHIRTS with logo
        small : 10 pcs
        xsmall : 20 pcs
        large : 30 pcs

------------------

Delivery receipt#: 2
Quantity :  40 pcs 
Item description :   
    Tumblers with straw
        2 BOXES * 20 pcs (this is 20pc/per box)

------------------

Delivery receipt#: 2
Quantity :  100 pcs 
Item description :   
    Marketing brochures
        10 bundles * 10 pcs (10 pcs per bundle)

I'm planning to make those data below the item description generic. But I dont have a proper term for it.

Can you suggest how my items table will look like?

Thanks!

Upvotes: 0

Views: 643

Answers (1)

christopher
christopher

Reputation: 27336

Often, the best approach is to pull out the nouns from your description.

  • Delivery receipt
  • Item Description

Now obviously, Receipt will be a table. So what does it contain?

  • Quantity
  • Item

But this means you can only have one item per purchase, which isn't reasonable, so we'll port this to a new table, ReceiptItems. With this, you use the primary key of the receipt number, and the primary key of what will become out item table.

Receipt Items

ReceiptID (Primary Key and Foreign Key)

ItemID (Primary Key and Foreign Key)

Quantity

Items

ItemID (Primary Key)

Item Description

Item Price

Item Type

Receipt

ReceiptID (Primary Key)

Buyer (Just another optional one)

Upvotes: 1

Related Questions