Jun 16, 2020
0
Level 3
Should I make two tables for Serialized and Bulk asset tracking?
I'm developing an inventory asset tracking application. In this application, you can have three types of products: Stocked, Serialized, and None.
- Serialized tracked products are physical objects that are assigned a unique barcode to each unit because they have unique serial numbers and attributes on each unit.
- Bulk tracked products are physical objects that have a unique barcode that references to the product whose quantity should be tracked.
- None tracked products are assigned a unique barcode that references to the product but is not tracked by quantity, but you can assign costs and prices to them.
Option 1:
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']
Option 2:
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 stocked_stocks table:
id // Primary Key
product_id
barcode
quantity // It will have the total quantity
condition // Enum ['great', 'damaged', 'broken']
Creating a serialized_stocks table:
id // Primary Key
product_id
barcode
condition // Enum ['great', 'damaged', 'broken']
Option 3:
Creating products table:
id // Primary key
name
details
slug
brand_id
category_id
tracking // Enum ['serialized', 'bulk', 'none']
value
rental_price
quantity // It will have the total quantity
is_active
Creating a stocks table:
id // Primary Key
product_id
barcode
condition // Enum ['great', 'damaged', 'broken']
Which of these three options should I go for?
Please or to participate in this conversation.