sqldatabase-design

Saving barcode number in database that is generated with the logic

The barcode number required to generate the barcode is generated logically in the backend and is not a random number

Example: 1025683256. In this number first 4 digits are category_id, last 2 digits are counter_id, middle ones are product_id. Each product has 5 counters - 12, 34, 56, 78, 90. So for each product, I have 5 barcodes depending on the counter_id. And each product defines a row in the database with 5 counter columns.

Now when I scan the barcode, I get this barcode number, and I want to search the item in database and display the item details. So my question was should I save the barcode number in the database and search the item in the database via barcode number? Or just break the barcode number into its parts and search for the item in the database?

Solution

  • If I understand you correct, you just want to find the product, which I assume is identified by the product_id.

    In that case you should extract the product_id and use it to find the product in the database.

    Searching for the full barcode would require you to search on 5 columns AND maintain this columns with redundant data.

    Including the category_id would make your search brittle. What if a product is assigned a different category. Now you wouldn't be able to find it.

    This is based on a couple of assumption I made, which you didn't explicitly state in your question:

    1. product_id is actually the id of the product. If it isn't fix your column names.
    2. category_id is actually the id of a category and used as a foreign key. If not, see above.

    Update

    From your recent comments I think I learned that you have multiple warehouses and for each warehouse which you identify with a counter_id and you have a stock amount which you name counter and you store these counters in the product table.

    While there might be (possibly good) reasons to do it this way, on its own it's a design flaw. You should have a stock table which has a composite id of warehouse_id and product_id and an amount column which holds the stock of the referenced product in the referenced warehouse.

    If you only want to display the stock amount, you could query that table with product_id plus warehouse_id (formerly counter_id)

    Or if you want to load product details and stock amount you can join product and stock table and query by product_id and warehouse_id