I'm designing a database for a poll system.
Questions can be one of two types: a numerical rating (1-5) or a voice record.
My idea is to have questions with a type field to specify if it's a numeric rating or an audio question. I'd have two fields, response to store the numeric answers and audio_id to link to another table that can store the audio files.
However, in this design, there will always be a null field in each record. Is this is a good approach to solve the problem?
There is nothing inherently wrong with NULL values present in every row, especially if that solves your problem.
If you are using SQL Server, and are expecting the majority of rows to contain a numerical selection, with only a very small number of audio answers, you might decide to make the AudioID column "sparse". See the Microsoft Docs for details about how to use sparse columns.
Definitive answers to database design questions like this are highly dependent on a variety of factors, none of which you've detailed in your question. For instance:
How many rows are you expecting to insert, update, delete on daily, weekly, and monthly basis?
How wide will each row be? If each row is hundreds of text columns wide that will necessitate a different answer than if each row is three integer columns wide.
Are you planning to query the data directly, or will you aggregate it from this table into a data warehouse?
If you can, please add the answers to these questions using the edit link directly beneath your question.
