Database : Store value that can be either numeric or alphanumeric

Question

I'm trying to decide how to store some data in my database. (SQLite) Let's say we have 2 tables : Attribute and Type

Table Type is more or less like an enum, it stores all types existing in my model (Integer, Real, Alphanumeric for example)

Table Attribute contains attributes, a record looks like this :

Attribute :
id : its id
name : name of the attribute
value : ??? (the main question here)
type : foreign key to table type

So an attribute can be of type Integer, Real or alphanumeric. I'm not sure how to proceed to store the value of the attribute depending on the type.

I've considered so far 3 solutions :

Solution 1 : The "value" field of attribute is of type String and I convert in the relevant type programmaticaly

Solution 2 : I create more "value" field like : intValue, realValue, and alphanValue, and put NULL in irrelevant fields depending on the type

Solution 3 : I create 3 more tables, IntValue, RealValue and AlphaNValue with foreign key to the related Attribute.

I want to know which solution is better in terms of performance and consistency, or if there's another relevant solution I haven't been thinking of.

Thanks a lot

Answer 1

I would say: keep it simple. SQLite is not fast for joining tables and also not a full DBMS. So better go with fewer tables in general.

If there is reason to have it less simple, I lack to see the reason. Maybe explain why you would go a more complex way in your case.

Answer 2

Your question is labelled MySQL, but you say it's about SQLLite - please label correctly, it helps others!

It sounds like you're implementing an Entity-Attribute-Value like solution. There are lots of discussions on this concept - this is one of the most useful.

One of the common criticisms raised on EAV is precisely the question you're asking - storing data in appropriate data types is hard.

So, if that's what you're doing, please look at alternatives to EAV - usually, the "flexibility" comes with so many drawbacks it's not worth it. Especially on a constrained environment like SQLLite.

If that's not realistic, I'd go for option 1. In all the options you paint, your application has to do some work outside the database, and option 1 is the simplest. Simple is nearly always better!