Background Information:
I'm learning Laravel 7 by creating an inventory tracking application. I have two models: Product and Stock.
The Product can be Serialized, Bulk, or None tracked.
- Serialized tracked products are objects that have a unique serial number and attributes on each unit.
- Bulk tracked products are physical objects whose quantity should be tracked.
- None tracked products are not tracked by quantity, but you can assign costs and prices to them.
The Stock is where the barcode will be stored.
- For serialized products, there will be multiple records belonging to a Product
- For bulk products, there will be one record with the quantity belonging to a Product
What I have tried:
Creating products table:
id // Primary key
name
details
slug
brand_id
category_id
tracking // Enum ['serialized', 'bulk', 'none']
value
rental_price
is_active
Creating a stocks table:
id // Primary Key
product_id
barcode
quantity // For serialized tracking, it defaults to 1. For bulk tracking, it will have the total quantity
condition // Enum ['great', 'damaged', 'broken']
The Problem:
I'm struggling to create a relational database design. I feel the approach I took can get messy and inefficient if the database grows.
Is this the correct way of doing this? Is there a smarter way to address this?
Should I instead, add two columns in the products table for barcode and quantity and only fill those columns when the tracking is bulk?
Edit 1: The practical use case is to manage all of the company's assets. It's focused on sound rental companies... not retail. The application will be a central system where they can keep track of the different attributes like firmware versions, serial numbers of each stock, and etc... They will also be able to prepare detailed invoices and track repairs and replacement parts for each product. I'm on a very early stage of development... therefore I'm focusing on creating the relationships and making sure I can create, update and delete products with their stocks.
The approach that you suggest sounds great and simple but focused for retail applications. I feel it takes out the ability to track each item individually.
For example: I have a Product called: "L-Acoustics K2 Line Array Speaker". I have 60 of them... so there will be 60 records created in the Stock table with a unique barcode because each speaker has a different serial number. I'm using a barcode to identify the stock, instead of serial numbers, because I want to track other items that might not have serial numbers or that the serial number is not obtainable.
At the same time, there are other products like cables, that I only care about the quantity. Because they don't have individual attributes like serial numbers or firmware versions. But it still needs to have one barcode so that I can scan the barcode when preparing for an event and specify the number of cables prepared for the event.
This is when I run into problems... because I feel that the way I design my database only allows me to have one of the scenarios... bulk or serialized products, but I want to have both
