Multi-user inventory list in MySQL?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
_$Frostbite
Forum Newbie
Posts: 3
Joined: Wed Jan 28, 2015 9:49 am

Multi-user inventory list in MySQL?

Post by _$Frostbite »

Hello, for a long time I've been wanting to learn PHP and MySQL and I've finally begun to do so. After following a 15 hour course on Lynda.com, I think I know the basics to be able to construct a small website. Now, I've got a project that I want to try out. My sister works for a company which sells different kinds of products, from makeup to soap. She has requested a small application where she can keep track of her inventory. When she recieves a new order for her inventory, she'll apply it to the application and it will update the database. I somewhat know how to do this, but I'm not sure how I want to build my database. My initial thought was to have individual tables for userdata, products, and then one for each users' inventory list? Is that a bad way to do this? In future, we would really like to be able to offer this to her colleagues as well for them to use. Is there a better way to build my database and how would I go about relating the users to their inventory data? Essentially, I think I'd be able to relate a users' data to another table for their inventory list. I don't see any other apparent way. I'd really like another set of eyes on this :D Please be kind enough to explain it somewhat thoroughly aswell, since I am relatively new to this :wink:

Some notes: I want the website containing an overview over all products in their inventory, making them able to add and remove, alter prices and such using for example forms. Now, I'll be partially able to code it myself, if you can help me decide how to build my database :) Thank you very much in advance, I look forward to interact with the people of this forum :D :D
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Multi-user inventory list in MySQL?

Post by Celauran »

Multiple users, each with a separate inventory? Is that correct? If so, I'd create a users table for user information, a products table for general info about products (SKU, price, etc), and an inventory table that references user and product IDs and a column for quantity.
_$Frostbite
Forum Newbie
Posts: 3
Joined: Wed Jan 28, 2015 9:49 am

Re: Multi-user inventory list in MySQL?

Post by _$Frostbite »

Celauran wrote:Multiple users, each with a separate inventory? Is that correct? If so, I'd create a users table for user information, a products table for general info about products (SKU, price, etc), and an inventory table that references user and product IDs and a column for quantity.
I guess I'll have to make a new inventory table for each user, then? :)
EDIT: Or, you probably mean that each time a new product is added to one's inventory, that input will be put into a collection of everyone's inventory with that user's ID, so I can filter out different users' products from the same table? Is that right? :)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Multi-user inventory list in MySQL?

Post by Celauran »

That latter sounds closer to right. I was thinking something like this.

Code: Select all

CREATE TABLE `inventory` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `product_id` int(11) unsigned NOT NULL,
  `quantity` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user` (`user_id`),
  KEY `product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
_$Frostbite
Forum Newbie
Posts: 3
Joined: Wed Jan 28, 2015 9:49 am

Re: Multi-user inventory list in MySQL?

Post by _$Frostbite »

Celauran wrote:That latter sounds closer to right. I was thinking something like this.

Code: Select all

CREATE TABLE `inventory` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `product_id` int(11) unsigned NOT NULL,
  `quantity` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user` (`user_id`),
  KEY `product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Riiiight, that makes sense, it'll be neat to have it all in one place! Thank you for quick and helpfull response. You can count on me returning when I hit more problems :P
-Frostbite
Post Reply