#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    3
    Rep Power
    0

    Help and Database structure and table Design


    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

    mysql Code:
    $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!
    Last edited by kencephalon; May 25th, 2013 at 08:46 AM. Reason: Cleaning up of post to make it more legible
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    you are right, separating out the descriptions and linking to them with integers does seem to be rather pointless, doesn't it

    the reason people do this is because the foreign keys enable a mechanism to control the actual description names that are allowed

    you can do the same with the actual description names -- i.e. separate them out to enable foreign key checking -- without using integers, but many people feel that this is unwieldy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo