what to include in a simple inventory database

Question

I want to create a simple inventory system to:

• Keep records of stock
• Alert user when a product is running out of stock
• Track sales and purchases
• Alert user when a product is almost reaching its expiry date(probably 7 days before)
• admin to create user accounts(all users must log in).

I am not really sure how many tables I should have and what to include. I have tried the following, but it doesn't seem to work:

Sales(Serial_Number,Name,Unit,Quantity,Amount,Sale_Date) Purchases(Serial_Number,Name,Unit,Quantity,Amount,Purchase_Date,Expiry_Date) Users(Work_Id,First_Name,Last_Name,Username,Password,Confirm_Password)

I am using visual studio 2010 and MySQL

Answer 1

I wrote an inventory system recently and a few fields have proven very useful. I made sure to include a 'created' datetime, for all rows and an 'IsActive' bit, that is set to false when you choose to delete something (so every delete is a soft-delete) and from there, made sure I had a nicely, but not overly, normalized schema to describe my data.

If you want a simple inventory and are thinking it can be covered with 2 or 3 tables, you'll probably end up with 20 to 30, possibly a lot more. Let your items be one table, itemtypes another, and itemcategories be another, for example. Be sure to have a separate history table for items, purchases, sales, etc.

Start with your most broad categories. Flesh them out with all the tables you could possibly need to describe, for example, your inventory. If you'll have boxes, nails, hammers, you might want a Category table that lists 'Containers', 'Hardware', 'Tools'.

For each hammer you add to your inventory, it should be a new row in your item table, that references your itemtype 'Hammer'. Then as each is sold, you can change the status of each item. If you want to know how many hammers you have, you query the item table for all items of type 'hammer' that have a status available. If a hammer is sold you change it to sold. If the hammer is returned in good condition, you change that specific item back to Available.

Don't delete rows, just set their 'IsActive' bit to false. That way, reporting on your data later on will still work correctly if it links to 'deleted' items, and during general usage you can just query items where IsActive = 1.

Hope this helps, this is my technique, though I'm sure there are many other. Comments welcome, it's always good to learn.

Answer 2

• Keep records of stock

  WIth your current data structure, this would not be very easy: one would have to look through the entire history and consider which purchase had been used to fulfil every sale (and assume that determination was in fact correct). It would be far easier to maintain an additional table of available stock:

  available_stock ( Serial_Number, Quantity, Expiry_Date )
  

  You could then use MySQL's event scheduler to remove expired stock automatically:

  CREATE EVENT remove_expired_stock
  ON SCHEDULE EVERY DAY STARTS CURRENT_DATE
  DO DELETE FROM available_stock WHERE Expiry_Date < CURRENT_DATE
  

  Be sure to use the InnoDB storage engine on each of your tables in order that you can wrap your database operations in a transaction to ensure atomicity:

  START TRANSACTION;
  
  SELECT SUM(Quantity) FROM available_stock WHERE Serial_Number = ? FOR UPDATE;
  
  -- only proceed if quantity is sufficient for a sale
  
  SET @q := ?;  -- quantity required
  
  UPDATE   available_stock
  SET      Quantity = GREATEST(@d, 0)
  WHERE    Serial_Number = ?
       AND (@d := Quantity - @q) IS NOT NULL
       AND (@q := -LEAST(@d, 0)) IS NOT NULL
  ORDER BY Expiry_Date;
  
  DELETE FROM available_stock WHERE Quantity = 0;
  
  INSERT INTO Sales ...
  
  COMMIT;
  

  You could even define a trigger on the Purchases table to insert new stock into the available_stock table automatically:

  CREATE TRIGGER new_stock AFTER INSERT ON Purchases FOR EACH ROW
    INSERT INTO available_stock
      ( Serial_Number, Quantity, Expiry_Date )
    VALUES
      ( NEW.Serial_Number, NEW.Quantity, NEW.Expiry_Date );
  

• Alert user when a product is running out of stock

  It's not entirely clear what you mean by "alert", but you probably want to run some sort of check periodically from your application code then dispatch a notification if required (by whatever means: email, SMS, etc).

• Track sales and purchases

  Your existing structure enables you to do that.

• Alert user when a product is almost reaching its expiry date(probably 7 days before)

  As above.

• admin to create user accounts(all users must log in).

  This would be enforced within your application, although one might well store the users in the database as you have done. Beware that there are many common pitfalls in rolling one's own authentication system: whilst it might not all be directly relevant to your project, The Definitive Guide to Forms based Website Authentication is nevertheless an excellent resource.

• I am not really sure how many tables I should have and what to include.

  Much of this will depend on your own project's requirements. Perhaps you need to sit down and think about exactly what level information you want to record and how it will be used?

  However, a couple of observations:

  1. rather than include "Name" and "Unit" in each table, make Serial_Number a foreign key into a separate Products table that contains that information.

  2. don't include both Password and Confirm_Password in the Users table: you only want to record a password if it has already been confirmed (and at that, you should record only a salted hash rather than the plaintext password itself - see the guide to authentication linked above).

• I have tried the following, but it doesn't seem to work:

  As @Olaf Dietsche commented, it's generally helpful if you explain what "doesn't seem to work. As it stands, we don't know whether you've written 10,000 lines of code and are struggling with one tiny piece or whether you've only created these tables and are expecting a whole inventory management system to magically appear!