o9s
is a microblog
home archives search feed blogroll


Things And Data Part 2

Read those two posts first for context.

I finally tried implementing the database schema below and I ran into an obvious problem. What about numbers? I’m not handling the different types.

Diagram

The value column can only be one type. It can only be a DATETIME, INT, TEXT, TIMESTAMP, etc.

My first intuition was to make the data type of the value field a TEXT. You can store an integer or a float in a TEXT field, it’s inefficient and you have to then manually cast the string you retrieve to the type you want, but hey, you can do it.

I have a better solution. I won’t have just two tables in my schema, I’ll have multiple Data tables. I’ll have a Data table for texts. I’ll have another Data table for numbers. I could have a Data table for images.

Diagram

It will allow me to handle different data types while keeping the flexibility that this type of schema gives me. The performance will not be affected as much as it would have been with using the TEXT data type for every value.

Posted on 2018-02-04   #database  






← Next post    ·    Previous post →