Mysql – Modelling the structure of a database involving products with multiple barcodes

database-designMySQLrdbmsschema

What would be the best way to store barcodes for products? I have a situation where the same product can have many different barcodes, let's say I have 1 product and this product can have 100 different barcodes (which must be unique).

Is it better to store those barcodes in separate table? For example, the following diagram

means I will have 1 product and 100 rows with different barcodes for this product (same name and everything).

Or is it better to have 100 products with the same name, each with barcode column, as shown below?

Best Answer

If you have a truly one-to-many relationship between your products and barcodes, it makes sense to keep it in a separate table for barcodes (don't repeat yourself-- your name and description would be identical for each repeated barcode).

IF however there is any variation in the product itself (name or description could vary) then you need to store each as a discrete record.

Related Solutions

Mysql – Database Design (Stores + Products)

Your problem can be modelled by an entity relationship diagram (created with dia):

ER-diagram .

There are two entities "product" and "store" and there is an m-n-relationship "stocks" between these entities. "product" has an attribute "productId" that uniquely determines the product. "store" has the unique attribute "storeId" that determines uniquely the store.

I have added the attributes "quanity", "storeAddress" and "productName" to make the model a little bit more complex. These attributes cannot be derived by your description, you can remove it from the diagram and from the resulting tables if you want the design follow your description closely. An n-m-realtionship means that a store can stock different products and a product can be in stock in different stores. We have the following problem desription:

Each store has a unique Id and an address
Each product has a unique Id an a name
a store can stock different products with different quantities

The last sentence can also be formulated in this way

a product can be stocked in different stores with different quantities

Such a diagram can be transformed in a relational model. Each entity is a table where the attributes are the columns. The unique identifier is the primary key. The m-n-relation is a table where the primary key is the a (storeId,productId) pair. If the relation has an attribute this attribute is a column in the table.

so we have the following relations (tables)

store("storeId", storeAddress)
product("productId",productName)
stock("storeId","productId",quantity)

Mysql – Product attribute schema design, mixing solutions

Put common things (price, SKU) in MySQL columns; index them if needed.

Put the rest in a JSON column. Do not try to use LIKE to search that.

Instead, first filter on what you can from the other columns, then use your application language to finish the filtering.

To save on space and bandwidth, compress (in the client) the JSON string and put it into a BLOB column.

Write your user-interface to encourage them to filter on the real columns, only reluctantly let them specify the other things.

See also MariaDB's "Dynamic Columns; it's been available for several releases. In the future, 5.7 promises to have a bunch of JSON tools.

Best Answer

Related Solutions

Mysql – Database Design (Stores + Products)

Mysql – Product attribute schema design, mixing solutions

Related Question