Complex SQL design, help/advice needed

Question

i have few questions for SQL gurus in here ... Briefly this is ads management system where user can define campaigns for different countries, categories, languages. I have few questions in mind so help me with what you can.

Generally i'm using ASP.NET and i want to cache all result set of certain user once he asks for statistics for the first time, this way i will avoid large round-trips to server.

any help is welcomed

Click here for diagram with all details you need for my questions

1.Main issue of this application is to show to the user how many clicks/impressions were and how much money he spent on campaign. What is the easiest way to get this information for him? I will also include filtering by date, date ranges and few other params in this statistics table.

2.Other issue is what happens when user will try to edit campaign. Old campaign will die this means if user set 0.01$ as campaignPPU (pay-per-unit) and next day updates it to 0.05$ all will be reset to 0.05$.

3.If you could re-design some parts of table design so it would be more flexible and easier to modify, how would you do it?

Thanks... sorry for so large job but it may interest some SQL guys in here

Answer 1

For #1, you might want to use a series of views to show interesting statistics. If you want to cache results, you could store the results to a reporting table that only gets refreshed every n hours (maybe up to 3 or 4 times a day? I don't know what would be suitable for this scenario).

Once all the data is in a report table, you can better index it for filtering, and since it will be purged and re-populated on a schedule, it should be faster to access.

Note that this will only work if populating the stats table does not take too long (you will have to be the judge of how long is "too long").

For #2, it sounds like an underlying design issue. What do you mean by "edit"? Does this edit operation destroy the old campaign and create a new "clone" campaign (that is obviously not a perfect clone or there wouldn't be a problem)? Is there historical data that is important, but getting orphaned or deleted by the edit? You might want to analyze this "edit" process and see if you need to add history-tracking to some of these tables. Maybe a simple datetime to old records, or a separate "history" table(s) that mirrors the structure of the tables being modified by the "edit" operation.

For #3, It looks alright, but I'm only seeing a sliver of the system. I don't know how the rest of the app is designed...

Answer 2

Eugene,

If you plan to keep the edited campaigns around consider not removing them. Instead make the campaigns date sensitive. For example UserA started campaign 1 on 1/1/2010 and ended it on 2/1/2010 then started campaign2 on 2/2/2010.

Or if you dont like the notion of end dating your campaigns. You could consider a history table for your campaigns. Basically the same table structure but an added UniqueIdentifier to make rows unique.

I should also note that estimated size of this campaign table and its related tables are important on your design. If you expect to have only 1000s of rows keeping old and current records in one table isnt a problem. however if you plan to have 1000000s or more then you may want to separate the old from the new for faster queries, or properly plan statistics and indicies on fields you know will need to be filtered on. Also remember indicies are usefule for reads but they slow down writes.