I am trying to save what type of data is on a field, text, number or list. If the type is text or number that is it, but if it is a list I need to save what options it has on a string. I was thinking what would be the most efficient way of saving this, for storage and performance.
My ideas are creating a column "list" on the original table, this column would be empty for most entries but if the type is list then it would have that string. Or I could create a separate table with just 2 values,one primary key and foreign key to contain the id of the row in the original table and a mandatory string fiel to save the options.
2
Answers
In terms of storage and performance, it is generally better to have a separate table with a foreign key when you have a column that is almost always empty. Here’s why:
By using a separate table, you can optimize storage usage, maintain data integrity, and improve query performance. However, it’s important to consider the specific requirements and context of your application before making a final decision.
For storage, a separate table could be very slightly more efficient; for performance it could go either way depending on many factors including the ratios of queries compared to updates. It is almost certainly not going to make any meaningful difference either way.
Instead, I would arrive at a solution based on what makes the most sense in understanding and working with the data. Here are the two solutions I would consider:
Use a separate table in all cases indexed to the primary key. This scenario works universally whether there are none, one, or many data items in your list. It is straightforward to query individual items in the list. You can add, delete, or update individual items in the list very efficiently. Consider storage flexibility first and then consider display needs. For example, store the data the way that makes the most sense. If you need the data formatted differently, format it on retrieval or use a view.
If you aren’t likely to be ever updating or querying the items in the list independently, simply use a single column to represent the data. This is usually very efficient, though it does not offer as much flexibility.
In any case, avoid using distinct data types, columns, or structures to represent the same data point. Standardize the data type so it can be any possible thing that you want to represent (one number, many numbers, one string, many string, etc.). If it is actually separate data points, then make them distinct and do not try to repurpose them to fit something other than intended.