Keeping Logs+ [closed]

Question

I'm building a plugin for a game community that is a roster. I need to keep logs on the members who have been accepted into the unit such as past ranks, citations, traings attended, etc.... The table looks like this:

 

record_id
user_id
clone_number -> clone number
arma3_id -> arma3/steam id
recruiter_id -> who recruited them
application_date -> When did they submit an application
application_status -> Denied  - Pending - Accepted - Issue
application_reason -> Who accepted/denied/had issue with the application
date_join -> if accepted, the day the application is accepted
citations -> Probation/trouble user
discharge_grade -> Honorable, etc...
discharge_date -> when was the discharge
discharge_rep -> who did the discharge
transfer_from -> where they transfer from
transfer_to -> where they going
transfer_status -> Accepter/Pending/Denied/Issue
transfer_rep -> who accepted/denied transfer
trainings -> which trainings did they complete
past_cshops_id -> cshops where id's
cshops_id -> what cshops are they currently in
awards -> list of awards by award ID
past_post_id -> what posistions/roles they where in
post_id -> current posistion/role in now
past_ranks_id -> past ranks
rank_id -> current rank
tis_date -> Time in service date
tig_date -> Time in grade date
trainings_attended -> attended trainings
trainings_status -> Complete/Failed/Not Attended
player_status -> Active/Inactive
player_portrate -> For In Game Portrate

 

First off I want to make the plugin check to see if a user has a service record - how would I do that?

Next, how would I go about keeping things logged in the database?

Answer 1

Personally I would create two separate tables.

1. In the main one, with most of the fields you listed, I would keep track of the current status (or 'latest';  e.g. last training completed)

2. Historic table, with user id and the historic data (ranks, trainings, citations) with dates for each record (from - to, or just a date field).

The exact structure will require some thinking but this way you have one main (current) table and one historic. This also allows for easy lookups to see if someone has completed a specific training for example.