Jun 15, 2020

Level 3

How to design the relational database for inventory asset tracking of serialized and bulk items

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?

martinbean

5 years ago

Level 80

@jginorio I don’t really understand your particular use case, but I worked on an inventory tracking application a little while ago.

Physical items usually have a barcode (and a SKU) to identify them. So you would keep track of how many units of a particular SKU you have in one location. So you may have multiple locations: warehouses, stores, etc. So with this, there are three entities:

SKU
Location
Stock level

Stock flows between locations, and customers, via channels (online shop, physical shop, etc). So you could have stock level adjustments to record the additions and deductions of stock. When stock comes in, you record a positive stock level adjustment. When stock is sold, you record a deduction. If an item is returned, that would be an addition. You can then find the level of stock of a particular item, and a particular item at a particular location if needs be.

1 like

jginorio

5 years ago

Level 3

@martinbean 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

joaquinwojcik

5 years ago

Level 1

@jginorio did you find any way to manage this?

I ran into the same issue a few days ago. I still haven't found a proper way to implement this.

martinbean

5 years ago

Level 80

@joaquinwojcik Well what are you trying to achieve?

joaquinwojcik

5 years ago

Level 1

Sorry for late response!

I'm developing an inventory management system for retail stores.

There are some stores that sell "serialized products" (e.g a TV, Phones, etc), and others that sell products per unit (e.g 5 meters of ethernet cable).

I've tried to implement Polymorphic Relationships to avoid having serialized_stock and bulk_stock relationships but I ran into a problem.

Here is my current approach.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Product extends Model
{
    use HasFactory, SoftDeletes;
    
    protected $fillable = [
        'business_id',
        'category_id',
        'name',
        'product_code',
        'stock_type',
        'price'
    ];

    public function serialized_stock()
    {
        return $this->hasMany("App\Models\SerializedStock");
    }

    public function bulk_stock()
    {
        return $this->hasMany("App\Models\BulkStock");
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class SerializedStock extends Model
{
    use HasFactory;

    protected $fillable = [
        'product_id',
        'store_id',
        'serial_number',
        'available',
        'status',
    ];
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class BulkStock extends Model
{
    use HasFactory;

    protected $fillable = [
        'product_id',
        'store_id',
        'quantity',
    ];
}

It isn't a extremely bad approach, but definitely it's not the best. I think that it will be annoying to work following this approach and probably it will lead my system be inconsistent.

Any suggestions to avoid making a mess?

Tray2

5 years ago

Level 73

I would use this kinds of database model

products
material_units
material_unit_contents

The product contains the information about the product such as part number, color, title, type and such. The material unit contains information about where it is and when it was created/recieved from the supplier. The material_unit_contents connects the product with the material unit. It has the current stock of that material unit.

So a product can have many material units through the material unit contents. By doing it this way you can have the quantity in different formats.

Let's say that you have two products.

Product 1 is a graphics card and you have ten pieces in two different material units. Product 2 is a network cat6 cable the you cut the length the customer wants.

You can use the same structure and you put a meassure of unit in the product table and the unit for the graphic cards can be pcs and the network cable cm.

You should also create a table for the measure_of_units so you can put a constrint on it in the products table.

When you sell a graphics card you decrease the quantity in the material_unit_contents table for the oldest material unit. unless of course you implement scanning of the material unit number you pick from.

Please or to participate in this conversation.