Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySql "Many to One" table design

I am new to DB design and I am having some trouble finding info on how to define a "Many to One" relationship. I can find all sorts of info on "One to Many" and "Many to Many" but nothing on "Many to One". My hangup is how to store the data. What I have is one table called "Categories" then I have another table called "Inventory", each "Inventory" item can belong to multiple "Categories".

How do I store multiple "Categories" in a single "Inventory" row? Should I have a intermediate table that stores the "Categories" ID with the corresponding "Inventory" ID? Or would adding something like a JSON string that has the "Categories" ID's in the "Inventory" row be the right way to do this? Or is there a way to store an Array of "Categories" ID's in the "Inventory" row?

Thanks allot for the help!

like image 334
Andy Braham Avatar asked Nov 22 '12 16:11

Andy Braham


2 Answers

the correct term of Many to One is One to Many. simply create a table like this,

CREATE TABLE Categories
(
    CategoryID INT Primary KEY,
    CategoryName
);

CREATE TABLE InventoryList
(
    ProductID INT Primary KEY,
    CategoryID INT,
    ProductName VARCHAR(50),
    CONSTRAINT tb_fk FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);
like image 125
John Woo Avatar answered Sep 24 '22 00:09

John Woo


First I would suggest that you download mysql workbench - it gives you a nice visual db design mode so you can see how things hang together (creates foreign key relationships etc etc for you).

In this instance this is actually a many-to-many relationship. as such you will need a category table, an inventory table and a category_has_inventory table (or inventory_has_category depending on semantics) where you store the id of the inventory and category in each tuple - workbench even creates this table for you when using the many-to-many relationship tool.

Pop back on here if you need further help.

IF a category can only contain one inventory item then you could create a one-to many relationship by adding inventory_id to the category table but that sounds wrong to me.

like image 20
Ian Wood Avatar answered Sep 24 '22 00:09

Ian Wood