I am building a web based app that has the potential to have very high traffic and collect a lot of data. Most of this traffic will be utilizing the database. Database is MySQL InnoDB. I want to give users the ability to define their own data fields along with a predefined set of columns that are common. As an example I would have a table (restaurants) with the following columns:
record_id //INT PK auto_inc
record_uuid //indexed stored as binary(16)
user_uuid //indexed stored as binary(16)
restaurant_uuid //indexed stored as binary(16)
date //indexed
price //indexed
party_count //indexed
rating //indexed
...
User "A" would then add his own fields such as (chef_name,phone,table_count,rank)
While user "B" might add fields such as (contract_amount,license_fee,cuisine_type)
My thinking is to have a number of tables to hold a row of data for each one of the custom fields. So I would have restaurants_extension_varchar, restaurants_extension_int and restaurants_extension_float.
Each of the three tables would look something like this:
extension_id //INT PK auto_increment
extension_uuid //indexed stored as binary(16) used by app to update data value
record_id //indexed INT - used as a key to the main restaurants table
field_identifier // TINYINT
the_data //indexed varchar/int/float - depending on which table this is
I would then partition each of these tables into 6 partitions. The field identifiers would be held in a memory array for that user and would map the record to a label and page placement data for that custom field. These custom fields will be updated individually through Ajax and not as a group like when you click submit on a multi field form. I expect these tables to hold millions of rows. I have decided to use an auto increment ID as the primary key for all tables plus a UUID ID as the application ID that I will pass to and from the web app to prevent users from manipulating the primary key ID in an attempt to effect other rows.
All of the custom fields need to be searchable.
I have two questions. The first being about the structure. From a pure performance angle, is this a good solution or would I be better off with one row with an abundance of columns.
The second question is regarding the use of an auto increment primary key ID along with an app generated uuid key which will be indexed. From what I understand if I use the uuid as the primary key it will be duplicated in each index that is created. I'm not a DBA so if there is any advice that can be offered I will be grateful. Thanks you!
