0

I am working on an project where I need to do execute an certain action when a user turns to some age.

When a user turns 12 an account need to be created and when a user turns 18 Parental supervision needs to be disabled.

What is the best approach to check if a user out my database turns a certain age. Do I create a service that checks all the users for a certain age and runs every morning. Or are there any other solutions for this issue.

Notice:

The program runs on an server so it running day and night.

Timon Post
  • 2,779
  • 1
  • 17
  • 32
  • to be honest, i recommend you check this when a user logs in, once per day tho, but it depends on your application, if a user has to login to do some action this might be a good approach, unless there are actions that needs to be done whether user logs in or not, then this might not be good idea. however this simple trick would be better query-wise. – Masoud Andalibi Jun 10 '17 at 12:31
  • It could be a solution but not for my program unfortunately. If a user turns 12 for example, a letter or email has to be send so that it know that an account has been created. And it is not an application that you would check every day more once a week. this action realy needs to be done directly or whit a day margin. – Timon Post Jun 10 '17 at 12:41

4 Answers4

2

Create a stored procedure that returns the list of users whose birthday is today and they are X years old. Then, everyday you can pass the age parameter (X) to the stored procedure to get the list of users who turn that age on that day.

Then, you just need a daily job to run the stored procedure with different parameters (12 and 18 for example), and do whatever you want with the returned list of users.

ataravati
  • 8,891
  • 9
  • 57
  • 89
  • Thanks !! , I will check how to create a stored procedure. It looks like a better approach than creating a separate table. – Timon Post Jun 10 '17 at 13:02
  • 1
    Except don't check whether their birthday is "today." There are too many things that can go wrong. What happens if your server is offline for a day for some reason? What if a user realizes they typed their birthday in incorrectly? Just run once a day and check to see who is over X years and hasn't had whatever process run on them. e.g. select * from user where dateadd(YEAR,12,USER.DOB) >= getdate() and CreatedAccount = 0. – aquinas Jun 10 '17 at 13:16
  • @aquinas, that depends on some details of the requirements that were are not fully aware of. We don't know what the application does, what range of ages are in the system, and... With your approach a 50-year-old user who's just added in the system will be also in the returned list. That may or may not be what the OP wants. – ataravati Jun 10 '17 at 14:15
  • Well, that's why I said there should be a flag like "CreatedAccount" a 50 year old should have that set as true when they get added if the OP doesn't want that process run. Point is, with the approach of "Who turned X age *today*" You are in big trouble if the server goes down, or you need to restore from backup, or you ever import users, or a user changes their birthday, and many other scenarios that I can't even think of :) – aquinas Jun 10 '17 at 14:19
  • @aquinas, as I said, those are details that depend on what the requirements are. The main approach doesn't change. It will still be a stored procedure and a daily job. – ataravati Jun 12 '17 at 16:19
  • 1
    I agree with the main approach, I was just letting the OP know that there are potential pitfalls with only looking at "users whose birthday is today." – aquinas Jun 12 '17 at 16:31
1

Checking for every user age would cost too much resources. So I think the better design is to create a separate table like this:

ID | UserID | Date     | Age
1  |   32   | 1.1.2024 | 12
2  |   32   | 1.1.2030 | 18
3  |   42   | 2.2.2021 | 18

When user gets created, dates of his 12th and 18th birthdays are recorded to this table. The service then runs, say, every 2 hours, and checks this table for passed dates. If it finds any, it invokes events and deletes records.

Dmitry Pavlushin
  • 612
  • 8
  • 23
  • Thanks for the answers does seem like a better approach I will check if someone else has an other solution otherwise your answer will be accepted. – Timon Post Jun 10 '17 at 12:44
  • Creating a separate table only for age doesn't make much sense. I'd suggest creating a stored procedure. – ataravati Jun 10 '17 at 12:51
1

If according to Technical Task, activity of customer account doesn't matter - means you need anyway to monitor age - I suggest you Create New table (add to DB) with fields whatever PrimaryKey, CustomerID ..., CreateAccountDate, ParentalSupervisionDate.

Then Create a new Task and 2 new stored procedure (SP): ...Add... and ...Check... where

...Add... SP will run on creation of account for person with age less than 18, and fill appropriate fields with dates when you need to act.

Ex. customer birthday is on ...2010. You fill field "CreateAccountDate" with ...2022 and field "ParentalSupervisionDate" with ...2028.

...Check... SP will run every night checking appropriate dates (comparing with current date) and returning customer accounts that you should modify.

Create temp SP that run once to fill in that New table with the data of already existing customers with age under 18 of course.

Then Add temp SP and ...Check... SP to that new task, run the task, and remove just temp SP from the task after it completes. First should Run temp SP. (DO NOT modify existing tables - very bad practice - in your case not needed)

At the end you will get one service that will run ...Add... SP on account creation and a Task that will run every night ...Check... SP that will return to second service (or whatever) a list of customers (customer accounts - better) that must be modified (usually are used DataSets).

And of course no need to delete anything - that's also a good way.

Timon Post
  • 2,779
  • 1
  • 17
  • 32
Gleb B
  • 151
  • 2
  • 8
  • Thanks for the answer, the checking needs only be done on user who turned to a certain age after that nothing special needs tot be done. Just like if you turn 18 your bank sends you a letter with some details change your banking account from junior to adult. – Timon Post Jun 11 '17 at 11:10
  • So first prepare everything concerning Adding to your new Table on customer creation (better sign to event on Create Account etc). Then create tempSP to run once to process all existing customers with age under 18. Then run Check SP and set it to run every night (when bank doesn't work) to get "list" of customers to do smth. After that checkin all concerning Add SP. By the way, create one more SP to check existing customers with age over 18 which are missing account or with parent control etc. Don't try to do all in one SP - try to follow SOLID principles. – Gleb B Jun 11 '17 at 13:19
0

there;s not enough info. If there's nothing special to do - I mean that since some age you don't need to do any specific financial operations with money or anything that concerns money operations - no need to check age all the time. I guess that for you is better to create a

service with event handler

on any (act) "activity" of client (not including when customer just checks his account, what's going on there), to check clients age and response accordingly. Unsubscribe after customers age is over 18.

It might also happen that client change the bank before any action - and doing smth for such client automatically - is an unnecessary job. Also, checking every day all customers is not a right thing.

Gleb B
  • 151
  • 2
  • 8
  • "Also, checking every day all customers is not a right thing." Why? – aquinas Jun 10 '17 at 16:14
  • Because there are customers, let's think of age 12,5 and a total amount of 10% of all customers, that aren't active for 5 years. Checking them every day you spend a lot of time and resources for doing nothing. That's in case, that on specific age you don't need to apply any changes "to the money" of that customer, but only organizational. It's better to react only on any changes associated with procedure you must apply at that age. – Gleb B Jun 10 '17 at 20:45
  • you can create SP or trigger that runs SP - but that is the result (reaction) on some event. But event must be raised before. And running every day SP to check XX customers in new table and implement one more SP (or modify existing one) that will fill in that new table - is not a good idea for that case. Telling again - it's not enough info about What to do with customer account on specific age. Is it possible to create a separate bank account for children under 12? – Gleb B Jun 10 '17 at 20:53
  • A lot of time? How long to do an index scan on a table with a few million rows? Couple second maybe? – aquinas Jun 10 '17 at 21:02
  • And consider that there are a lot of other checks for different situation - I think they exist (for any limits, withdraw, etc.) - and maybe it's better to check how they are implemented ? Maybe you already have what you need ? It's better to keep one common approach (structure) in design process, from UI to Server. – Gleb B Jun 10 '17 at 21:06
  • FWIW, on my machine I created a table with 50 million rows with random dates and queries for all dates less than today - 200. It grabbed ~51,000 rows in less than 1 second. – aquinas Jun 10 '17 at 21:29
  • What is written in TECHNICAL TASK??? The full cycle consist NOT only of your select (comparing dates etc). – Gleb B Jun 10 '17 at 22:54