How to design user’ s registration and resignation movements? 1

[ericnet]

I have a users table with a field to register the date the user was registered for the first time, and another field to register the user’s ‘reason’ of that registration.
Now I’ m considering to add a field to register drops (when the user decides not continue with us, or we decide it). To do that I am considering to add a field to register ‘dropping’ date, another field to register the user’s reason of dropping (resignation), and another field to determine if user is currently registered or dropped.
So, the users table would include:

UserState (Currently Registered or Resigned/Dropped)
RegistrationDate
RegistrationReason
ResignationDate (If any)
ResignationReason (If any)

How you would do it to handle user’s registration and resignation?

Note: Once they have resigned, they will not have access to the web application.
To come back later they would have to 'register' again, since this might happen will be rare cases, but I have to consider them.

Thank you

 

[Crowley16]

well, your solution would work, and you could skip the current field and assume if any resignation is blank, then they are registered.

However, for a more normalised approach, you would ideally split out the registration/resignation into a different table, say accountActivity, which contains an activityType link to specify what the AccountActivity...

so you would have:

user(userID, ...)
userAccountActivity(userID, actTypeID, date, ...)
AccountActivityType(actTypeID, actDescription, ...)

or something along those lines...

--------------------
Procrastinate Now!

 

[ericnet]

Hi,

And something like this in that separate table? :

RegistersResignations Table

RegRes_id
Reg_Or_Res (here type if we are referring to a Register or a Resignation)
User_num (FK of user_id of users table)
Date (the date of the register or resignation)
Reason_num (FK of reasonsRegRes Table of registers and resignations)
ActivityType_num (FK of KindOfAccounts Table ‘platinum, gold, silver,..’)
...

 

[Crowley16]

sure, it's your database...

I kept the table seperate from reg/resigns because it could be used to keep any potential user related data but you don't have to...

--------------------
Procrastinate Now!

 

[ericnet]

Ok
So, if I understand, you have users, RegRes, and accountActivity Tables? Instead of my two tables (user and RegRes)?

 

[Crowley16]

no, if you look at my first post, there's 3 tables:

users, keeps user header information
userAccountActivity, keeps any reg/resign details and dates and any other data you want
AccountActivityType, keeps a list of the types of activity there are. (e.g. Reg would be activity 1 and Resign would be activity 2)

--------------------
Procrastinate Now!

 

[ericnet]

Yes, I see.. Thanks to your last clarification I can see a more interesting solution (which I see you tried to explain from the begginig). I wanted to create two more tables after your first post (RegistersResignations and RegOrRes_type), but I’ve seen you have those two tables open to whatever event or situation, while I ‘closed’ those two tables to only two possible events (a registration or a resignation). So, I will create those two tables with a more open meaning titles, as you did, so that in the future I can add more events or situations if needed.

Thank you very much