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'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?

Upd.

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.

share|improve this question
    
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 Sep 17 '13 at 18:25
    
Updated question –  colix Sep 17 '13 at 20:17
add comment

closed as too broad by dezso, Kin, Max Vernon, StanleyJohns, RolandoMySQLDBA Sep 18 '13 at 5:01

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 0 down vote accepted

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

table impressions
-channel_id
-date timestamp
-other attributes like ad_id

table clicks
-channel_id
-date timestamp
-ad_id

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.

share|improve this answer
    
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? –  colix Sep 17 '13 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 Sep 17 '13 at 20:29
    
Made an update. –  netneladno Sep 17 '13 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? –  colix Sep 17 '13 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 Sep 17 '13 at 20:46
show 3 more comments

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