CODEWITHSUNDEEP
mysqldatabasedatabase-design
David542
David542

Reputation: 110382

New database or new table for analytics?

I would like to store some analytics figures. For example, the amount of times a user performed action X or the amount of users that have logged in in the past day, etc.

Would the best practice for this be to add another table to my existing application database? Or to add a new database here? I'm thinking the second option would be a bit overkill.

Upvotes: 1

Views: 970

Answers (3)

Rory Hunter
Rory Hunter

Reputation: 3460

Use a separate database. It keeps your schema cleaner, makes it easier to manage database users and their access privileges (as you don't need per-table rights), and it could make it easier to manage replication if you ever need to go down that road.

Upvotes: 1

user1024732
user1024732

Reputation:

It depends entirely on your business/security/public interest needs, use cases, and funding.

For example, if, by analytics, you mean "Working out how many anonymous people read this tiny public wiki", then a table will probably be fine. If that wiki grows, then you might want a separate DB for performance reasons.

If, however, the analytics DB will include information about site members -- especially information that does NOT need to be stored directly on the public website -- then you have a legal responsibility to take all reasonable precautions to safeguard the data. That may mean storing it in a separate business-internal DB, rather than on your public-facing site DB.

At the very least, you should have separate DB connection settings, separate connections, and separate cursors for your analytics in the web app, so that it's easy to split them off to a separate DB later. This is often done to separate reads and writes in webapps, so you should be able to find examples of it, if you're stuck.

Past a certain small site load, you're going to want it in a separate DB for performance, no matter what. Unless you're using some kind of distributed, horizontally-scalable DB that's built for performance anyway, that is.

Upvotes: 1

xkeshav
xkeshav

Reputation: 54052

just add another table in existing db , if you add another database then you have to switch from one db to ther db simultaneously means you have to take care of mysql_connect() or you need to close previous db connection before connection other db, it would be a little bit hectic

Upvotes: -1

Related Questions