I'm going to create statistics on RoR. I have 3 models: User, Project and Channel. User owns Projects, whereas Projects have many Channels. Now I'm designing all these models using MySQL.

The goal of the service is to provide user the variation in time of different parameters of each Channel. E.g.: Channel Google Adwords has 1000 show/day, 100 clicks/day and 2 people which came from Google Adwords bought something that day. So I could have just stored all the information in columns, like: Channel "Google Adwords", shows: 1000, clicks: 100, buyings: 2. But I need to make distribution by days, please, give me an advice how to design such database? Maybe I should use another solution, maybe NoSQL?


Input: User daily fills in data about ads from different sources (I call them channels, e.g.: Google Adwords): number of impressions and clicks (later data will be imported automatically usiing API). Output: Various stats like graphs and CTR of different channels, all channels and so on.

  • Why would NoSQL be better than MySQL? First of all you need to determine what information you need. What design issues have you considered? For any database you need to understand the information that you need to track and what your results should be. For example, you need measures by days, suggests that activities needs a date associated with then. And so on. (What does RoR mean in your system?) Try rewriting your question to include the needed inputs and outputs.
    – RLF
    Commented Sep 17, 2013 at 18:25
  • Updated question Commented Sep 17, 2013 at 20:17

1 Answer 1


I'd recommend you to make a separate table for each attribute. Like so

table impressions
-date timestamp
-other attributes like ad_id

table clicks
-date timestamp

For every event you just make an insert, to show stats it is also very efficient - it is just index scan with count or aggregate function. Also this approach scales well - you can easily shard such tables. Or replace it with nosql solution. For such a task i'd recommend HBase - this datamodel naturally translates in HBase schema, and HBase is very good at range scans.

UPD: I see you updated qustion, so i need to clarify. Basic tables which still usefull to store Facts. Over them you can immediately make graphs and reports. If you concern about performance, people usually preaggregate data. So if your people need 5/10/30 minutes resolution, you aggregate into 5/10/30_minute_table for fast reporting.

This batch processing of old data takes time. If it doesn't fit your SLA, modern guys take CEP engines like twitter Storm to analyze and aggregate this table in real time. Or you can just buffer events for a minute, aggregate them and then store. I don't know how RoR fits this role and i don't know your requirements. But i think thats another story.

  • This is interesting approach, thank you. I was thinking to do something like that, but I wasn't going to split tables by attribute types, do you think I should do it for the sake of scalability? Commented Sep 17, 2013 at 20:21
  • It's even not about scalability but about Normal Form also. You see that all you care about is events, this events are of different types, so you create separate table for each of them. Every such table have PK and FK and conform to every functional dependency you have. And it is ultimate source of truth. I see that you may want to have aggregated table for you user report, and this design makes it easy for you to create a view or Report Table updated once every time as want. Think about it as Log Tables, which hold Facts. If report takes a lot of time to generate, aggregate events in Report.
    – netneladno
    Commented Sep 17, 2013 at 20:29
  • Made an update.
    – netneladno
    Commented Sep 17, 2013 at 20:42
  • What do you mean by Report Table? Should I create it and somehow aggregate data there to use it to output information to user? Commented Sep 17, 2013 at 20:42
  • Yes, you aggregate data in table "like: Channel "Google Adwords", shows: 1000, clicks: 100, buyings: 2." About your concern of time, you create a time attribute, as your report resolution need (hour, day, week, etc)
    – netneladno
    Commented Sep 17, 2013 at 20:46

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