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:
mysql Code: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 :
mysql Code: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
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.