A couple of notes,
Rather than track only failures, it's generally a good idea to know when people sign in, too. There is probably already a table that handles authentication tokens, but there's no reason why
failed_login_attempts couldn't contain both successful and unsuccessful authentication attempts.
isLocked on a secondary table you leave yourself open to a bunch of crazy hack "solutions" to fix data inconsistencies when an account should be marked as locked or not ... particularly for users who regularly lose their passwords. Logically, if an account is "locked", then that should go on the
User) table so that you're not always hitting a table that will quickly have a lot of data to determine whether a login can take place.
From here you can record all login attempts into
LoginTXN (or whatever you decide to call it), mark the
success field as
0 on failure,
1 on success (or
Y), and go from there.
When checking to see how many login attempts have been made, you'll need to do something like:
FROM LoginTXN txn INNER JOIN (SELECT z.account_id, MAX(z.id) as recent_success
FROM LoginTXN z
WHERE z.account_id = 999
GROUP BY z.account_id) sok ON txn.account_id = sok.account_id
AND txn.id > sok.recent_success
Note: This is pseudo-code. Don't simply copy/paste it.
Which will then give you a count. If you want to have conditions like "5 failed attempts in the last hour", thereby locking the account for — at most — 60 minutes, you could do that with a
WHERE statement that examines the
created_at value in
Of course, if this is being put into a view or a stored procedure, then an
is_locked field on
Account may be wholly unnecessary as the authentication rule could be encapsulated completely within the view or stored procedure.
Hope this gives you a couple of pointers that will lead you to a more complete solution.
Friendly Aside: Try to stick with a consistent naming scheme when making tables. If you prefer to use words separated by underscores, like
is_locked, then stick with that. If you prefer camel-case, like
isLocked, then stick with that. You — and every other developer who works on this code in the future — will appreciate a predictable consistency. As you can probably see from my examples above, I tend to go with camel-cased tables, and underscored columns.