Excel spreadsheet into a MySQL database tables

Question

I am relatively new at database design and would like to get this right from the start. I have had differing advice on how to do this and believe that the common suggestion of importing all the data is just wrong as the data won't be normalised and will have a lot of redundant repeated data. I come from an MS Access background rather than MySQL.

This question is about the design of the database tables not on the PHP processing to get it into the tables.

I need to add a lot of custom data to a Wordpress website. This data is currently in multiple Excel spreadsheets and has a lot of duplicated data in both rows and columns and appears to me to be ideal for splitting into 4 tables. The main table will contain initially around a million rows and will grow regularly. I intend to have a front end upload facility for certain users to upload their files and would like to automate this.

Each spreadsheet contains 10,000 to 15,000 rows. A lot of the rows have duplicated data with just two fields transposed with another two fields so these rows can be deleted in the database.

I have attached an image of part of one of the spreadsheets.

My current thinking is to have one table for Column E as every row contains exactly the same data. A second table for Column N as there is lots of repeated data here. A third table for column's A B F G H with linked data to the first two tables and a fourth table for columns C D I J K L M with linked data in the other 3 tables. As you can see rows 2 and 3 are the same except for transposed data between columns I and J with columns C and D but this is not always the case See rows 8 and 9 for instance).

Is this the best way to break up the data or should I just be looking at one large table. The data will be queried in different ways (4 result sets). It is unlikely that once the data is entered it will need to be updated.

I would welcome any thoughts or advice before I actually start building this.

Answer 1

I did not look in detail at the data since I belief you can normalize it yourself, but from what I've grasped out of your posting I would say you are doing everything right.

One warning though: Likely you will encounter a lot of existing inconsistencies in the excel sheets. You must fix those in order to import and normalize the data correctly. This constitutes a high initial effort.

The alternative (keep the data in the form it is) instead will rise the maintenance costs of the application.

In the long run I claim that the additional initial costs will be less than the regular addition in maintenance cost.

Answer 2

I would load all data into a single table. I find it easier to discover duplicates and such, relational analysis, creation of tables and normalizing. For the number of records you indicate, I would usually just leave as a single un-normalized table and remove duplicates while attempting to discern and define the appropriate table key and foriegn keys for maximum performance.

I find most actions easier using SQL commands than writing excess programming logic. You may still need some programming for more complex things, but most can be done with a few simple SQL commands using HeidiSQL. I also recommend Excel2MySQL (software of my own design) to create and load the table accurately with the least fuss.