What is the best way to store a single value in a MySQL DB?

user_9926534 0
Viewed 1237

I want to store a single column with just one value in MySQL DB, and this column is not related to any other table in the DB, so just to store a single value should I create an entire table or is there a better way to store single key-value pair in SQL DB.

For example, a boolean variable isActive needs to be stored and queried

mysql sql
1 Answers

It is not uncommon to do this, but don't create a new table for every singleton value. Make one table for this purpose, with two columns, where the first column identifies a name for the value, and the second column is the actual value. Let the data type of the value be string. This is a minor disadvantage when you actually need a boolean, but that way your table is more flexible.

For instance:

create table params(
    paramName varchar(100) not null,
    paramValue varchar(100)
);

insert into params values ('isActive', '1');
commit;

See also Variant data type in DB which touches on the need to store different data types in the same column. The consensus is to use the string data type unless the specific data type is really essential, in which case I would suggest to create a separate "parameterXXX" table per data type, so all booleans go in one table, all dates in another, ...etc.

user_54598390
Related