What is the best way to store single non-duplicate data in a database?

Question

What is the best practice for storing data in a database when only one record is required. An example is the configuration data that applies to the entire application / website. Is it well known to create a table for it containing only one record?

I ask in the context of the MongoDB database, although I think this question is also applicable for SQL databases.

Answer 1

An example of a helper table commonly found in databases will be called Constants and may contain such pi values, the idea starts with the fact that all applications using the database should use the same scale and precision. In standard SQL, to ensure that they are no more than one row, for example. ( from Joe Celco ):

 CREATE TABLE Constants ( lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY, CHECK (lock = 'X'), pi FLOAT DEFAULT 3.142592653 NOT NULL, e FLOAT DEFAULT 2.71828182 NOT NULL, phi FLOAT DEFAULT 1.6180339887 NOT NULL, ... ); 

Since mySQL does not support the CHECK constraint, a trigger is required to achieve this.

Answer 2

The table will be beautiful, there is no reason not to use it just because it will have only one row.

I just had the strangest idea (I would not have implemented it, but somehow thought about it). You can create a hard-coded representation like this:

 create view myConfigView as select 'myConfigvalue1' as configValue1, 'myConfigvalue2' as configValue2 

and do select * from myConfigView :)

but again, there is no reason not to use the table just because it will have only one row

Answer 3

If you are using SQL DB, you will likely have columns such as the key name, and the value and each attribute will be stored as a string. In MongoDB, you can save all related configuration as a single JSON document

Answer 4

For MongoDB databases, I usually just create a new “table”, but for SQL databases this entails much more (especially when others also work with the same database, SQL is not so flexible), so you might want to be more careful with it.

Answer 5

I am using a config table with a name (config_name) and a value (config_value). I even add a help field so that users can see what the name / value pair is for, or where it is used.

 CREATE TABLE config ( config_id bigint unsigned NOT NULL auto_increment, config_name varchar(128) NOT NULL, config_value text NOT NULL, config_help text COMMENT 'help', PRIMARY KEY (config_id), UNIQUE KEY ix_config_name (config_name), ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

Then the following php code returns a value for the key or returns an empty string. $ Db is supposed to be an open database connection. All entries are forced to enter lowercase letters.

 function getConfigValue($name) { $retval=''; $db = $this->db; $sql = 'select config_value from config where LOWER(config_name)="'.strtolower($name).'"'; $result = $db->Query($sql); if ($result) { $row = $db->FetchAssoc($result); $retval = $row['config_value']; } return $retval; } 

In this case, all mysql / php, but the general principle remains.

Answer 6

I would just create a table for the configurations, as rainecc said, and use the cache to take this table into memory :) and use it from there (cache). It will be the best.