Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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)

share|improve this answer
    
Thanks @Joe, I did read into it on Wikipedia (en.wikipedia.org/wiki/Partition_%28database%29) and some of the links you posted. The type of partitioning you would be referring to would be the horizontal partitioning. This is a feature I didn't know existed until now. I will now pose a new question: dba.stackexchange.com/questions/4134/… which asks proper partitioning practice. –  CenterOrbit Jul 26 '11 at 19:39
add comment

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.

share|improve this answer
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 '11 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 '11 at 22:35
    
It's even less complicated if the ArchiveHistory table is in the same database. –  Cape Cod Gunny Jul 26 '11 at 22:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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