Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

jginorio's avatar

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?

0 likes
0 replies

Please or to participate in this conversation.