Making inventory lists from a master list in mysql

Question

I have inventories at different locations and want to be able to have a seperate inventory list for each location. The inventories should contain the same stuff at each location, so I want to use a "master list" that stores the name and standard quantity of each item. Then each location should have an actual quantity for each item.

I can copy the whole list for each location, but I want to be able to update the master list instead. I have tried searching the web for the solution, but I can't find what I'm looking for, or I don't know what to search for. Any ideas how I can solve this?

Thanks

Answer 1

You need something like this (simplified version containing only required fields):

Database structure:

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(50) NOT NULL,
  `required_quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


INSERT INTO `items` (`id`, `item_name`, `required_quantity`) VALUES
(1, 'Item 1', 10),
(2, 'Item 2', 20);

-- --------------------------------------------------------


CREATE TABLE IF NOT EXISTS `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Zrzut danych tabeli `locations`
--

INSERT INTO `locations` (`id`, `location_name`) VALUES
(1, 'Location 1'),
(2, 'Location 2');

-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `locations_items` (
  `location_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `actual_quantity` int(11) NOT NULL,
  UNIQUE KEY `location_id` (`location_id`,`item_id`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `locations_items` (`location_id`, `item_id`, `actual_quantity`) VALUES
(1, 1, 5),
(2, 2, 7);


ALTER TABLE `locations_items`
  ADD CONSTRAINT `locations_items_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `locations_items_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON UPDATE CASCADE;

Query to get every location and every item with required (basic quantity) and actual quantity per location:

SELECT 

q.location_id,
q.location_name,
q.item_id,
q.item_name,
q.required_quantity,
IFNULL(li.actual_quantity, 0) AS actual_quantity

FROM
(SELECT
    l.id AS location_id,
    l.location_name,
    i.id AS item_id,
    i.item_name,
    i.required_quantity
FROM
    locations AS l
CROSS JOIN items AS i
) AS q

LEFT JOIN locations_items AS li ON q.location_id = li.location_id AND q.item_id = li.item_id

SQL Fiddle Demo

You have common items list in table items where you can enter standard quantity which will be required for all locations. And if you need to update it, do it in the same table and it will apply to all locations at once.

Insert/update actual location in table locations_items which is unique per location and item. There you can record actual quantities for every location.

And query lets you to fetch all items required/actual quantities in all locations. You can of course finetune it with additional conditions to get particular location(s) or item(s).