Clever Geek Handbook

Inventory management ideas using mysql

I am creating a database for a publishing company. The company has about 1300 books and about 6-7 offices. I have now created a table that displays stock items in all locations. The table should look like this to the user :

Book Name          Location1            Location2          Location3 ......
History              20000               3000               4354
Computers             4000               688                344
Maths                 3046               300                0
...

I already have a Books table that stores all the details of the books, I also have an office table with office information. Now if I create an inventory control table that displays information as above, I end up in a huge table with a lot of repetitions if I save my data like this:

Column1- Book_ID   Column2- Location_ID     Column3- Quantity
     1                  1                        20000
     1                  2                        3000
     1                  3                        4354
     2                  1                        4000
     2                  2                        688
   ...

So, I think this is not the best way to store data as it will eventually be 1300 (Books) X 7 (Locations) = 9100 rows

. Is there a better way to store data. Now I can have 7 additional columns in the stable book, but if I create a new location, I have to add another column to the Books table.

I would appreciate any advice or if you think the above method is appropriate or not.

+3


source to share


3 answers


No, this is the best way to do it.

You have a many-to-many relationship between books and locations. This is in almost all cases stored in the database as an "associative" table between the two main objects. In your case, you also have additional information about this association, namely "stocks" or "quantity" (or, if you think of it as a graph, the amount of the connection or the extreme weight).



So, it may seem like you have a lot of "duplication", but you really don't. If you tried to do it any other way, it would be much less flexible. For example, if you have a project, it doesn't require changing the database schema to add another thousand different books or 20 more locations.

If you tried to put the number of books in the Locations or Locations in the Books table, you will need to change the database layout and then retest any code that might use it.

+4


source


This is the most common (and effective) solution. Most frameworks like Django, Modx and some others implement Many2Many relationships only through an intermediate table using foreign key relationships.

Make sure you have specified the table correctly.



ALTER TABLE stock_management add index (Book_ID), add index (Location_ID)
+2


source


This is really the best way to do it; you have 9100 independent data to store, so you really need 9100 rows (fewer, indeed, rows where count is 0 can be omitted). Another way to organize the data would require changing the structure of the table when the location was added.

+1


source






More articles:


All Articles