CODEWITHSUNDEEP
mysqlsqloptimization
Cheerio
Cheerio

Reputation: 1240

MySQL schema for one table

When I need a table to set settings for my app. I always use table with many fields: 

create table options (

 id ...
 url ...
 keys ..
 path ...

)

Of course I need one ID (because I have one settings)

Could I use other approach like:

create table options (

 id ...
 field ..

)

When field is for example: 

`key => value`

So I don't need to set many fields ..

BS: The fields have different types: int text datetime .. etc

Upvotes: 0

Views: 122

Answers (4)

Randy
Randy

Reputation: 16677

So I don't need to set many fields ..

you are able to issue an UPDATE statement that does not set every field.

update options set someval = 1 where id = 2;

this leaves all other values the way they were...

Upvotes: 0

rid
rid

Reputation: 63542

Sure, you can do that. Just make sure you cast to the correct type when taking the values out of the database, and, similarly, when inserting or updating them.

PHP example:

// saving a settings value

$max_length = 20;
mysql_query("UPDATE settings SET value = '{$max_length}' WHERE field = 'max_length'");

// obtaining a settings value

$result = mysql_query("SELECT value FROM settings WHERE field = 'max_length'");
$settings = mysql_fetch_assoc($result);
$max_length = (int) $settings['value'];

For the above example, your schema will need a field (varchar) and a value (varchar).

Upvotes: 1

pabdulin
pabdulin

Reputation: 35235

You can use something like this:

create table options (
OptionID string,
OptionType string,
OptionValue string,
)

then you need a function that will convert value to required type.

Upvotes: 0

Stijn Geukens
Stijn Geukens

Reputation: 15628

This is a better approach, otherwise if you want to add another setting you would need to change your model (database: add column) instead of just adding a row to an existing table.

Use:

  • id
  • key
  • value

And possibly some other fields like type, required, ...

As type use Varchar and if you map a Setting (a DB-row) to a (Java, ...) Object provide methods like getValue(), getValueAsDate(), getValueAsBoolean(), ...

Upvotes: 0

Related Questions