I'm responsible for creating a database on a project. We have fields that are rarely going to have a value (1 in every 10,000 records) and I'm trying to work out the best way to store this in the database.
As far as I can see I have 3 options:
- Add a column in the table for each extra value
- Add a linked table which references the original table and has records only where we need to store a value
- Use the XML data type in the original table and store all of the values in this.
Are there any other options that I've not considered?
I'm trying work out the pros and cons of each method. As far as I can tell 1 would be the easiest and 2 would take the least amount of space but I'm struggling to find many resources for 3.