I have a database table that currently looks like this:
PageField
-Id (Int, PK)
-FieldType (String) Can be Text, Decimal, Integer or Bit
-Value (This stores the value regardless of the FieldType, so it is not strongly typed)
Should I remove the Value column and replace it with 4 separate columns TextValue, DecimalValue, IntegerValue, and BitValue?
The data type would be set to what is relevant. This would mean in every row 3 of these 4 columns would be NULL.
This question (in various guises) crops up regularly. This type of "solution" is known as EAV (Entity-Attribute-Value) and is not a good idea. Take a look here or here for tips (or links to tips) on the problems it can cause and how to properly leverage the data types that your RDBMS offers.
Not using the correct data type is a sure fire way to confuse the query optimiser and slow your system down. Joe Celko is a world renowned expert in all matters SQL and his opinions merit attention.
The fact that he uses the acronym "MUCK" (*) to describe this system should tell you all you need to know :-)
(*) Massively Unified Code Key
To add just a bit to the correct answer from Vérace (+1), you should go further than separating different types into different columns, you should also define what your columns are and use them appropriately. This is called design and it is a necessary step for an understandable, scalable, good performing system.
Just to hit on the understandable angle, compare this:
SELECT
(SELECT Value FROM PageField WHERE ID=1 AND FieldType='Integer'),
(SELECT Value FROM PageField WHERE ID=1 AND FieldType='Float'),
(SELECT Value FROM PageField WHERE ID=1 AND FieldType='String')
FROM dual;
With this:
SELECT IntegerValue, FloatValue, TextValue FROM PageField WHERE ID=1;
With this:
SELECT Length, Cost, Fabric FROM Patterns WHERE ID=1;
If you are trying to create a flexible application where users can create their own "forms"/"screens"/"pages", I would recommend you create different "modules" with specific meaningfully table name for the module and specific meaningfull labeled columns for each fields rather than a table to store fields which can be anything and everything.
With your current design you will produce a very slow application, and retrieving data for reporting purposes will become tedious as well.
Allow your users to select a "module" and then pick a set of specific fields from that module to produce their pages.
You will sacrifice flexibility for a bit more administration work but it's worth it.
It depends what do you want to do with "Value". if you need for example sum(...) it may be best to have a decimal column and use "case when fieldtype='N' then sum(cDecimal) When ..".
Search on the tag [eav] -- there are a lot of discussions of the evils of it. One insidious evil comes when you need to sort numbers that are stored in a VARCHAR.
