CODEWITHSUNDEEP
databasedatabase-design
user3311539
user3311539

Reputation:

Database Design - how to store quantities that are measured in different ways

I would like to know if the database design i have in mind for an online food store is good according to the usually followed standards and conventions.

Basically the confusion i have is how to store items whose quantity is measured in different ways.

For example, there are items that are measured in terms of kilograms and then there are items measured in terms of number of packets.

For example rice is measured in kilograms and something like say, Noodles would be measured in terms of number of packets.

so the tables are planned to have below fields:

Items table with the fields: category,name,company,variant and a boolean variable named measured_in_packets?..

for items where measured_in_packets is set to true, an entry in another table will hold the available packet sizes:

packet_sizes table with item_id and packet_size..

so if one product is available in multiple packet sizes (250 gm, 500 gm etc), a row would be made for each available size against the item id...

does this sound like a good database design?

Upvotes: 2

Views: 3184

Answers (1)

Paul Richter
Paul Richter

Reputation: 11072

In a nutshell, you have items which have a quantity value, but that quantity value can be measured in different kinds of measurement types. You gave examples such as kilograms, packages, and we can perhaps add others such as litres for liquids, etc.

One of the problems with the current solution is that is doesn't allow for any easy alteration or expansion. It also relies on the checking of a boolean field in order to make decisions (such as which table to join I believe, based on your description).

Instead, a better approach would be to create a table containing the possible measurement types, such as kilograms or packets. Your items then simply have a foreign key to this table, and that tells you how the item is measured. This allows you to expand the types in the future, and no need to maintain a boolean flag, or do any other manual work.

This diagram illustrates what I'm referring to:

enter image description here

So if the data in these tables looked like this:

items

+----+---------+----------+----------------------+
| id | name    | quantity | measurement_types_id |
+----+---------+----------+----------------------+
|  1 | Rice    |       50 |                    1 |
|  2 | Noodles |       75 |                    2 |
+----+---------+----------+----------------------+

measurement_types

+----+-----------+--------------------+
| id | name      | measurement_symbol |
+----+-----------+--------------------+
|  1 | Kilograms | kg                 |
|  2 | Packets   | packets            |
+----+-----------+--------------------+

A practical example of this data using the following query:

SELECT items.name, items.quantity, measurement_types.measurement_symbol 
FROM items 
INNER JOIN measurement_types 
    ON measurement_types.id = items.measurement_types_id;

would yield this result:

+---------+----------+--------------------+
| name    | quantity | measurement_symbol |
+---------+----------+--------------------+
| Rice    |       50 | kg                 |
| Noodles |       75 | packets            |
+---------+----------+--------------------+

Upvotes: 4

Related Questions