CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-design
Juliano
Juliano

Reputation: 65

Storing user settings in database

What is the best approach to store user settings in database (PostgreSQL)?

Option 1:

id       serial
user_id  int
key      varchar
value    varchar

Option 2:

id            serial
user_id       int
data_type     regtype
key           varchar
value_bool    boolean
value_int     int
value_string  varchar

Option 3: None of the obove.

Upvotes: 0

Views: 2519

Answers (2)

Stephan Lechner
Stephan Lechner

Reputation: 35154

From a perspective of "an application reads in the data", I'd vote for Option 1; An application is usually aware of the keys and knows the corresponding datatype; then dividing up values into different columns would probably complicate the way of reading from / writing to the DB.

From a perspective of querying the data in a way other than just retrieving a complete user record with a particular id, I'd vote for Option 2. This because conditions on the values would require data transformations in Option 1, but not in option 2. Hence, queries probably become easier.

Anyway, if it were just about storing and retrieving complete user records and you'd never or rarely query the values of a user, one could also think of storing them as json or xml in the DB.

Upvotes: 1

Omar Ali
Omar Ali

Reputation: 8617

If you're not interested in querying using any of those fields:

  • data_type
  • value_bool
  • value_int
  • value_string

Then I may suggest you go for Option 3: Use JSON/JSONB to store this as an object in the database.

Not only it gives you flexibility to add more fields later, but doesn't also restrict data types.

In some cases, this can come handy.

Upvotes: 1

Related Questions