Originally posted on the main site.
This document describes how schema extensibility is typically achieved in commercial enterprise software applications; in particular, in customer relationship management (CRM) applications. A future update will discuss newer extensibility mechanisms such as native XML column types in databases.
So here is a typical data model pattern:
create table master ( mid int not null auto_increment, … , primary key (mid) );
create table detail ( mid int not null, did int not null auto_increment, … , primary key (mid, did) );
( detail.mid is a foreign key to master.mid but no one uses these in MySQL… )
So this works great if you’re using the default MyISAM storage manager but it does not work using the InnoDB storage manager (RTFM!). Unfortunately, MyISAM does not provide ACID properties which means you should not be using MyISAM unless you have another way of recovering from data loss. My data is valuable so I have to use InnoDB, which means I have to restructure the detail table so that detail.did is the primary key and detail.mid is just a field with an index.
I really wish MySQL had one storage manager that actually worked like a relational database is supposed to, instead of several half-working storage managers.
When you are analyzing a query plan in SQL Server, you need to be aware that time spent in User Defined Functions (UDFs) is not included in the query plan. This is a problem because programmers seem to like using UDFs to make their SQL more modular and because it is very easy to write UDFs that kill your performance. (See this article “UDF Performance Costs” for a simple example.)
A reliable way of measuring the costs of a UDF is to run your stored proc with and without the UDF, and to measure the time it takes to run it. I have seen 10x performance improvements between sprocs with UDFs and sprocs rewritten to remove UDFs.