SQL: Analytics event value that can be string or number

Question

I'm using a PostgreSQL database and I want to create an analytics table that has a many-to-one relationship with an analytics_type table where analytics_type_id is the foreign key. The table also has a many-to-one relationship with a user table with user_id as the foreign key. The structure of the analytics table looks like this:

|id|user_id|analytics_type_id|value|

The value can be a string or a number, but the data type will be varchar. This sounds good so far, but my team wants to be able to filter the value without having to rely on type casting. This is because we're using metabase and it doesn't have casting functions available in their UI.

So, we thought of changing the structure of the analytics table to look like this:

|id|user_id|analytics_type_id|string_value|float_value|

Here, we'll store string_value as a varchar and float_value as a numeric data type. This will allow us to easily filter the data via the metabase UI. Are there any drawbacks to going with the latter approach? Please let me know if there's a better approach too.

Answer 1

This approach looks reasonable, and incidentally I have done something similar to store polymorphic values in a relational database. The storage overhead for NULL columns is often negligible, and the query advantages will likely outweigh it.