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.