13

I am mostly a self-taught when it comes to database designs. I am posing this question because I have settled on this common structure, but am wondering if it is the most efficient or 'industry standard' method.

Most databases I design have a user table, and then a persons activty is tracked in another table. I understand that the beauty of the database is to have these sorts of efficiencies, but the activity table will gather many many events fairly quickly just from every user using it regularly, thus becoming a huge table fairly quickly with moderate user usage. Is this best practice to just let it grow in this way? Or is a tier of tables, or splitting to different tables based on dates, or per amount of users, or something else?

+--------------------+                   +------------------------+
|   UserData         |                   |   Activity             |
+-=------------------+                   +------------------------+
| ID     (auto uint) | <--1-to-many-+    | ID  (auto uint)        |
| UserName (text)    |              +--> | UserID (uint)          |
| Email    (text)    |                   | Timestamp (time)       |
| additional info... |                   | Type (ID to elsewhere) |
+--------------------+                   | additional info...     | 
                                         +------------------------+

I just would like to know of where I can improve anything, as to help me learn.

Improve this question

2 Answers 2

Reset to default
5

Or is a tier of tables, or splitting to different tables based on dates, or per amount of users, or something else?

You may want to look into the concept of 'partitioning' in your database. Most RDBMSes have some support for them (eg, mysql, oracle, sql server, postgresql). Basically, you let the RDBMS handle the process of creating/managing the fact that each month/year/whatever is stored in a separate table, while the code accessing it treats it as one large table.

You could partition it by user name, date, or whatever's going to be used most frequently to access the data. (there are advantages / disadvantages of making it user-centric vs. date-centrid ... but I don't know if you want me going into all that)

Improve this answer
1
6

You have made a very good observation. The Activity table will grow fast and large. What I have done in the past is archive off the older data (say older than 14 days) to an ActivityHistory table. Doing so keeps the Activity table to a manageable size and if you need to do research you can always look back at the ActivityHistory table.

Improve this answer
3
  • 1
    I like your idea, and it is a solution that will fit nearly any database setup even ones that don't support @Joe solution. Yet this would also complicate some of the queries involved if you needed to access the older archived data and create the necessity of adding a union join. Very good though, I didn't think of this approach. Thank you.
    – CenterOrbit
    Jul 26, 2011 at 19:19
  • This is not necessarily complicated, you can play with the connection strings from the app to choose the history db in the case that the data is older.. Or you can use linked servers in the procedures, and in case some datetime is older than x days, go to the Archive linked server instead of the main server.
    – Marian
    Jul 26, 2011 at 22:35
  • It's even less complicated if the ArchiveHistory table is in the same database.
    – Michael Riley - AKA Gunny
    Jul 26, 2011 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.