CODEWITHSUNDEEP
sql-serverdatabasedatabase-designdatabase-schema
Aryan SuryaWansi
Aryan SuryaWansi

Reputation: 2741

I have a task to maintain ITEMS stock

Items are comes in Piece and Boxes.

e.g Suppose,

  1. I have purchase 100 piece (tablet) of medicineX.
  2. I have purchase one Box which contain 50 medicineY.

Now item dispense by piece wise only.

Means MedicineX will be given to customer by how much piece they demands one or two or five as per their demands.

Same for MedicineY, I will open the Box of 50 medicine and will give to customer as they demands means 2 , 5 Piece, …etc

Now I required to have a Table in SQL-Server Database.

I have confusion in what are the field should be in table I will create. Please help…thanks……

Upvotes: 1

Views: 332

Answers (2)

djna
djna

Reputation: 55937

When you serve a customer I don't think you need to track which box you take the tablet from. So for the point of view of tracking how much stock you have you just need a ITEM_STOCK table. So you need to figure out how much detail you need. 

 BrandName
 GenericName
 Strength
 Units
 Form
 quantityInStock

So records could have 

 Neurofen, Ibuprofen, 75, mg, capsule, 450

You might add extra fields for re-order thresholds or whatever.

If you do need to track units such as boxes I'd be inclined to keep that in a separate table because you may well have different sizes of box.

While your system might be capable of tracking how many boxes are open you are at the mercy of the users accurately capturing the data. Do they always use all of one box before opening another? Would they bother to record that fact? In a large multi-user scenario I'm sceptical that the users would get it right.

Upvotes: 1

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

Usually, you keep the stock quantity in the smallest possible unit (i.e. pieces) and have a second field for the box size (and even more fields for tray size, pallet size if it matters).

For example, a stock item with "quantity=350", "boxsize=50" means that you have 7 boxes of 50 pieces each. You could also have "quantity=343", "boxsize=50", which means you have 6 original boxes of 50 and an opened box with 43 pieces left.

Upvotes: 2

Related Questions