CODEWITHSUNDEEP
databasedatabase-designarchitecturedata-modeling
infinityLoop
infinityLoop

Reputation: 325

Database modelling string as enumeration or action as boolean values?

what is the best approach to model certain user actions in a database? let say you could an extendable set of actions like: login, logout, profile update and deactivation and so on....

first approach storing the actions as string in one column....

CREATE TABLE user_actions
(
  id bigserial NOT NULL,
  user_id integer,
  created_on timestamp with time zone NOT NULL,
  action character varying(20) NOT NULL,
)

or creating a boolean column for each action, more more when adding new actions but maybe faster and easier searched...

CREATE TABLE user_actions
(
  id bigserial NOT NULL,
  user_id integer,
  created_on timestamp with time zone NOT NULL,
  login boolean NOT NULL,
  logout boolean NOT NULL,
  profile_update boolean NOT NULL,
  profile_deactivated boolean NOT NULL,
)

would like to get your feedback and suggestions

Upvotes: 0

Views: 34

Answers (1)

Rob Conklin
Rob Conklin

Reputation: 9456

I would split the two. Create another table call it "AVAILABLE_ACTIONS" with an integer key that you can use as a foreign key to your USER_ACTIONS table. This gives you better performance, while not having to issue DDL every time a new action comes around.

Upvotes: 1

Related Questions