在数据库中存储用于用户活动日志的IP

Question

I'm building a sort of login system for my website and I want to log user's activity in the database. The problem is with storing the IPs of the user each time.

I have the users table with all the user's details, such as username, pass etc. But if I'll add another column with the IPs, the only way to store it(that I am aware) is JSON kind of format(the TEXT one). So it would be something like:

username: theuser password: thepassword etc... IPs: { '27/04/2018 13:05': '127.0.0.1', '28/04/2018 16:28': '127.0.0.2', etc }

But...

1) Is it efficient to store IPs like that?

2) If yes, then how can I update each time the date and IP, so it won't erase the previous ones?

3) If instead of using the same table, I will create a new one, say "users_IPs", and then will store the user-id there, and then will use the same TEXT(JSON-like format) to store the IPs, would that be more efficient?

If you think there's a better way of dealing with this kind of thing, you're most welcome to share you thoughts.

Answer 1

Storing logs as JSON is neither efficient nor practical.

List of IPs as JSON string will take more disk space. At the same time such list is harder to search - at some point you may want use these logs to search users by IP or display logins-per-day statistics. This will be much harder if you will need to operate on JSON string than separate table with proper schema.

This also will slow down updates. For each new IP you need to update the whole JSON, so if you have a 100 saved IPs, and want to add a new one, you need to save in database JSON with 101 records. Inserting single record in separate table should be much more efficient (and easier to prune old data).

So unless:
a) you want to keep this simple and you don't want to store a large amount of data in this way nor update it often,
b) you need to access user logs often and select performance is much more important than logs updating,
I would recommend creating a separate table for login logs, where each row represents single login event. It is more safe solution - scales better and it is more pragmatic.