I have a database table containing data for a submitted application form with a predicted load of two million rows per year.
There is an option to tag an application with a custom text but this feature will probably only be used 5-10% of the time. Later on forms can be searched out by this text.
Should this be implemented as a nullable attribute on the main table, or is it better to extract this to a seperate table containing only the key and text?
The answer probably depends on the specific DBMS you will be using. For example, how much space does a null text column occupy on your DBMS? Also, how big is this text column - only a few characters, or potentially thousands?
One possible advantage of having a separate table for the text is that it may be more efficient for searches based on the text, if they require a full table scan (since it will only have 5-10% of the number of rows, and these rows will be smaller than they would be in a single table). However, you have to balance that against the fact that you would then need to join back to the main table to get other data.
The only way to be sure is to set it up both ways and benchmark it.
Having the nullable text column in a separate table would be better in many ways
1) It will not be nullable if moved to a separate table
2) It is semantically more elegant
3) Joining back will not be inefficient as you will be joining on the PK of the parent table and that will use a hash join in all probability.
4) It will be extensible if you decide to have more such optional columns with each application in the future.
You could look at a design similar to the following:
Application(app#, date,...)
Application_Attachments(app#, text_col1)