Advice on database design and table structure needed

Question

Im new to programming and databases in general. I've read PHP.MYSQL for DUmmies 4th edition thus far, and am trying to create a database of my own which stores the Menus/Inventories of shops. I am currently using XAMPP/MYSQL.

Sample tables of my database are as follows:

SHOPINFO

Shopname    Outlet          Category    Subcategory       Item
PizzaHut    Main            Drinks      Carbonated        Cola
PizzaHut    Main            Drinks      Non-carbonated    Orange Juice
BurgerKing  Central London  Burgers     Beef              Whopper
BurgerKing  South London    Burgers     Beef              Whopper Jr.
BurgerKing  South London    Drinks      Carbonated        Cola

I am currently wondering if i should split the above table up into :

SHOPINFO
Shopname    Outlet           Category    Subcategory    Item
PizzaHut    Main             1           1              1
PizzaHut    Main             1           2              3
BurgerKing  Central London   1           5              4
BurgerKing  South London     1           5              7
BurgerKing  South London     3           3              2

Where the Categories,Subcategories and Items are all split up into different tables where they are identified by their CategoryID,SubCategoryID and ItemID respectively.

The 2 Major Questions i have regarding this decision are:

1.Would it really be beneficial to split the table up? I am asking this as would it not be far easier to query the first table rather than the second table? E.g i could simply do something like

$query="SELECT * FROM SHOPINFO WHERE Shopname='BurgerKing' AND 
Outlet='South London' AND Category='Drinks' AND 
Subcategory='Carbonated'";
$result=mysqli_query($cxn,$query) or die("Error");
while($row=mysqli_fetch_assoc)
{
extract($row);
echo "$Item";
}

instead of the query that would have to be done if the table was split(Which i have no idea how to do, and which makes my mind hurt).

2.If i were to split the table up, i'm guessing that i would have to create separate tables for each shop for the Category,SubCategory and Item, using the Shopname & Outlet as primary keys, which i would then link to the SHOPINFO table with foreign keys, probably using ON DELETE CASCADE & ON UPDATE CASCADE. The main question is, exactly what benefits would doing this bring about, apart from making the querying more complicated?

PS: My concerns are regarding future scalability.(E.g Adding countries,Cities,States to Outlets in the future)

Advice,Help,Opinions,Insults and Flaming are appreciated.

Thanks!

Answer 1

I think that your entire structure is wrong. You need to split everything up into separate tables, starting with a table of companies - Pizza Hut and Burger King are the only entries which you have shown. Then you need a table of branches which would contain a foreign key to the company along with atomic data about each branch, like its branch number, address etc. Then you need a category table, whose data seems to be either drinks or burgers, followed by a subcategory table (carbonated, non-carbonated, beef, chicken, etc) which contains a foreign key to the category. The you need a products table (cola, orange juice, whopper, whopper jnr) which has a foreign key to the subcategory. Finally, there would be a branch-products join table which has two fields (branch number, product number).

This structure, whilst seemingly cumbersome, is the most flexible and allows one to write all kinds of queries with little difficulty - total sales by company, total sales by branch, etc. This may not be clear when you are defining your database but you will appreciate this in the future when you are asked for some data aggregation which you had not foreseen.

Keeping everything in one table is a very bad idea: let's say that Burger King is taken over by The Grill Master (an invented name): this way you only have to change one record to reflect the change, whereas when using one huge table, you would have to update many records.

The 'huge one table' approach comes from people who use Excel as their database manager and basically have no experience of anything more complicated. The professional answer is to use a relational database with everything split up into tables.

I apologise if the above seems condescending; I am a doctoral candidate who is looking at the use of Excel within companies which use relational databases, so I am somewhat biased in my views.