I am using MySQL 5 and designing some tables that will hold quite a large amount of data which in time will probably exceed 10,000 rows and some others will be over a million rows.
These tables have some optional data, such as “description” that will be applicable to some rows but not all. I have designed the tables so that there is an Int id that links to a content table where the optional data is stored. If the row has no description, then the content id is set to 0, but if data has been stored for it, it’s set to the relevant key id of the content row in the content table.
This means I have designed the large tables to be a bunch of Ints – linking to other tables for the different data they hold. Since much of the data is optional, this avoids having TEXT and large VARCHAR’s being stored in the big table. Also, because different parts of the system will require different parts of the data this means they aren’t dealing with one big table – they just fetch data from the relevant sub-tables as needed. For example – when calculating stock, the description, images, title and so on are not required.
My logic for this is based on trying to make the heavy tables as light as possible, trying to avoid them becoming physically huge with both data and numbers of rows.
I would like to check this is the right thing to do as it will require a little more work on the server in terms of doing a few extra short joins and also a few extra queries in some cases, but I would hope having the tables nice and clean will help with performance.
Is this correct? Is this a favourable way of designing large databases?
Also, I plan on using Transactions to ensure everything stays valid and reliable and ensure linked rows don’t get separated from parents/children. I know I will need to use InoDB tables for this, but should I be using InoDB for all tables, or should I be using MyISAM for everything except the important transaction based tables?
Is InoDB much less efficient compared to MyISAM?