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
Multi-user inventory list in MySQL?
Moderator: General Moderators
-
_$Frostbite
- Forum Newbie
- Posts: 3
- Joined: Wed Jan 28, 2015 9:49 am
Multi-user inventory list in MySQL?
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
Please be kind enough to explain it somewhat thoroughly aswell, since I am relatively new to this
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

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
Re: Multi-user inventory list in MySQL?
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?
I guess I'll have to make a new inventory table for each user, then?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.
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?
Re: Multi-user inventory list in MySQL?
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?
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 problemsCelauran 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;
-Frostbite