Should I use a separate table in a .PHP application for all areas?

Chinmay S June 17, 2014
Pinterest Stumbleupon Whatsapp

I am creating a .PHP based application which will record accounting entries for specific areas. To handle entries, I am creating a separate table for all areas.

The table name will include the area name as well as the date on which it was created and the table will contain a unique ID, particulars, debit and credit.

So, my question is: Should I create separate table for all areas, will it affect the performance?

  1. James B
    June 18, 2014 at 1:12 pm

    I assume you mean MySQL, since PHP isn't a database. I'm not a database expert by a long shot, but my answer would be: separate data only where it makes logical sense to do so. If the only way in which this data differs would be an "area" column, it would make logical sense to make a single table.

    • Chinmay S
      June 18, 2014 at 1:45 pm

      I have also read that millions of rows in a table are better than hundreds of tables with less data but no site ever tells why. Does it creates some kind of performance issues? And what is the problem in creating many tables?

    • Bruce E
      June 18, 2014 at 5:28 pm

      Pick up any book on databases and read the section on data normalization. The reason you want multiple tables is to reduce the amount of redundant data. For example, it is better to have customer addresses in a single table, all of their sales transactions in another and have the tables linked together using a Customer ID field. Now when there is a change of address, it only needs to be changed in one location instead of every transaction record. If you are doing this for any other reason, it is probably the wrong design decision.

      In your case, if there is not a specific reason to include the creation date for the area, you should just add the area as a column in your primary table and completely skip the creation date. If there is actually a use for the creation date for each area (it will be used in the database for processing), you may be better served with an additional table.

    • Chinmay S
      June 19, 2014 at 3:23 am

      You have also said that more tables is wrong design decision but I want to know why? Why people prefer 1 table over many?

    • Bruce E
      June 19, 2014 at 4:17 am

      I thought two parts were abundantly clear.

      1. "The reason you want multiple tables is to reduce the amount of redundant data. For example, it is better to have customer addresses in a single table, all of their sales transactions in another and have the tables linked together using a Customer ID field. Now when there is a change of address, it only needs to be changed in one location instead of every transaction record."

      2. "If there is actually a use for the creation date for each area (it will be used in the database for processing), you may be better served with an additional table."

      Point 1 tells you why you would want multiple tables. There is no other reason for using multiple tables. Using additional tables to reduce the duplication of data in the system is a good thing. Trying to use more tables for anything else is not.

      Point 2 addresses your particular situation. If the creation date for each area is not used during database processing, it is useless to even have it in the database at all. If it is, it would potentially be a candidate for removal to another table otherwise it should not be stored in the database at all.

    • Chinmay S
      June 19, 2014 at 5:35 am

      I store table name in the format "area_name__june_19_2014", so date is required during database processing and I think it will be a whole lot easier to manage all the data.

      Because there will be many entries in a particular area so it will be better to put that in its respective table. And If I use single table for areas and single table for entries, I have to link them using id.
      So I think using seperate tables would be a good idea.

      But just now I have discovered a major problem. If by mistake I enter same area name twice(the table name would be fine because of the difference in dates), then it will retrieve all entries of a single table for 2 areas. This will happen because I have strstr() in place to find the occurence of area name in the table name.
      Ex:- If I enter New York as the area name on June 18, it will be put in the database as new_york__june_18_2014 and on the next day, I enter New York again, it will be new_york__june_19_2014 but because I am using explode() function it will be displayed as New York but when PHP will retrieve entries using strstr() it will show all the entries for New York on 18th June even if I click on New York of 19th June.
      So I think I have to find a better way to retrieve table name.

    • James Bruce
      June 19, 2014 at 8:11 am

      Redesign your data structure, you would never put the name / date in the table name. They would be separate columns in one central table. It's just faster and less error prone that way. With your method, you'll up with literally thousands of tables in no time at all.

    • Bruce E
      June 19, 2014 at 6:52 pm

      From what you have explained here, you are not actually using the creation date for any kind of processing, so it is useless data. Remove it from the entire database. Just add a column in your primary table to contain the area. So we will call this column 'Area'. Now when you want to find everything you have for New York, your SQL would look like 'SELECT * FROM MyTable WHERE Area = 'New York' Now you don't have a separate table for every area which would only be creating more complexity and therefore more bugs.

    • Chinmay S
      June 20, 2014 at 6:35 am

      Date is required because it is being used in the table name but anyways, from now on I will use 1 table for all areas, 1 table for entries of those areas.

    • Bruce E
      June 20, 2014 at 6:11 pm

      Using a date in a table name is not useful in any way I can conceive. If the table of areas only contains the areas themselves and it's ID, it is useless as well. From the information you have supplied, you should be using one table. Period.

Ads by Google