Question 170660 in Mysql Question posted in
The official MySQL documentation can be found here.

Is it better to have a SQL table w/ a column almost always empty or a separate table w/ a foreign key?

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.

Tags:

2

Answers


  1. 0

    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:

    1. Storage Efficiency: Creating a separate table for the options would save space in the database because you only need to store the options for the specific rows that require them. If you have a large number of rows in your main table where the "list" column is empty, it would be inefficient to allocate space for that column in each row.
    2. Database Normalization: By creating a separate table with a foreign key, you can adhere to database normalization principles. This allows you to maintain data integrity and avoid redundant data. It also provides better flexibility for managing and querying your data.
    3. Performance Optimization: When querying the data, having a separate table with a foreign key allows you to easily join the tables and retrieve the necessary information. This approach can be more efficient than searching for empty values in a large table with many rows.

    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.

    Login or Signup to reply.
  2. 0

    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:

    1. 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.

    2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search