I have a mysql database, inside this database. I have every subscription and badge and I'd like to log everytime a badge or a subscription is been used.

This is my database :

Can I log everytime a badge is validated inside "validateat" or I have to create a table for every badge and subscription?

CodePudding user response：

You need a table to log badge activity.

create table badges (
  id integer primary key auto_increment,
  name varchar(255) not null,
  validated_at datetime not null
);

create table badge_activities (
  badge_id integer not null,
    index(badge_id),
    foreign key(badge_id) references badges,
  activity_at datetime not null
)

Then insert into badge_activities every time you want to log something.

insert into badge_activities (badge_id, activity_at) values (3, current_timestamp)

You can also add a column to describe the activity. This could be a varchar, an enum, or a foreign key to another table of activites.

However, unless you need to query this data consider writing to a log file and using a log indexing service instead.