Storing "irregular" data in MySQL

Apologies in advance if this question has already been asked, but I really don't know how to search for the problem I have.

I'm developing a hotel booking and reservation management system with the intention of working with multiple clients, and it is expected that some clients will have different requirements, which include additional fields beyond the standard found i开发者_JAVA百科n the booking response form (name, address, email, post code et cetera).

To accomplish this, I've created an "options" field that stores these additional fields and their attendant values as serialized data. However, the client wants those fields to be searchable. While this is possible, this quite clearly isn't the optimal way of storing data that needs to be searched.

Also, the table in question is of InnoDB format in MySQL.

About the only thing I can think of is moving these additional fields into a separate table, but that presents a lot challenges to the reading and writing process of bookings.

What is an optimal way of storing this kind of irregular data for the purposes of searching?

One option is to use a key-value table. Store the keys separately, e.g.:

CREATE TABLE keys (
  id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  keyName VARCHAR(100)
);

CREATE TABLE values (
  key_id INT(11) UNSIGNED NOT NULL,
  customer_id INT(11) UNSIGNED NOT NULL, 
  value VARCHAR(100)
);

Definitely still not great, but it's a valid solution to your problem.

Your problem isn't really a database problem but rather an issue of how to support multiple clients and their requirements in a single program.

The first thing you need to decide is whether multiple clients will be supported within a single instance of your program, or whether each client will have their own instance.

If each client has their own instance, then you can maintain the common codebase separately and customize each client's instance as required. It may take some planning to produce a system in which changes made to the common codebase are correctly inherited by the customized versions, but in the end each client will get exactly what they want.

If you're taking the multi-tenanted approach then you need to decide in advance the exact degree to which each client will be able to customize their system. You then provide for that customization in your database and application structure. At the simplest level, this allows each client to store their identifying information and a logo in a table someplace (possibly their own CSS link to really give the application a customized look).

In the case of "extra fields", this can be handled in several ways. One is to simply put 10 extra VARCHAR fields on the table in question, and allow each client to "name" these fields as they see fit (possibly with TYPE specifiers that your application will use to coerce the VARCHAR data if needed). However many of the columns they've named are shown (with the correct prompt) where appropriate in the user interface. This approach has the benefit that, once set up, you won't have any additional work to do per client.

Another approach would be to allow each client to have an additional 1 to 1 related table that stores the extra fields. In this case you can correctly name and type the fields in the database. The disadvantage is that to the extent you allow the client to freely choose what these fields are, you'll have to modify your user interface to "know" about each individual client.

I would probably separate it out in to a table of its own if it's something that you won't always be needing when reading that table, i.e., do you ever query this table without returning this field?

If you do choose to keep it in the same table there's no reason you can't place an index on it however which would speed up the searches as much as is possible without splitting up the data into logical chunks (which you can't if it can be "anything"). Do be warned that adding indexes will increase write times while decreasing read times (so don't do it in a system that will write more frequently than read or where the writes are more time sensitive).