Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer 1

up vote 0 down vote accepted

A lot of people want to do what you are describing. They get into big trouble when the dataset gets big.

All of the custom fields need to be searchable.

Only one solution: MariaDB with "Dynamic Columns". For discussion of why it is impractical otherwise, and discussion of lesser solutions: http://mysql.rjweb.org/doc.php/eav

an auto increment primary key ID along with an app generated uuid key which will be indexed

Once the table (or at least the index) is too big to be cached, UUID/GUIDs lead to lots of random disk I/O and destroy the cache. Further discussion (and a workaround): http://mysql.rjweb.org/doc.php/uuid

share|improve this answer
    
Rick, thanks for your quick answer and for the info you have provided. I looked at MariaDB and it looks like a perfect solution for my needs! –  Charles09 Oct 23 '12 at 23:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.