It occurred to me that thinking about all the layers of this program probably isn't a good idea. A database should be able to function just fine on it's own, so as long as I have that correct, and I simply collect the information with about thinking about how I plan to use it, then whatever I create should be fine. In other words: don't think of the database as a piece of the program. Doing this seems like it is opening up a bunch of traps I am falling into and most of all: this database, if I think about the program that goes with it too much, won't be easy to port or extend if/when I need to.
The details of the data I am collecting are as follows:
There is currently one site where I am creating and running ads for. The quantity of the sites may increase. This site is industry-specific so I can't say much about it that isn't too obscure. To illustrate, I'll use the terms 'AdSense' and 'bannerAd.'
And of course, this is my job and I most certainly can't ask someone to do it for me.
I have two kinds of ads that I am running right now. Once again, that may change in the future. The first ad simply displays our products and cost per View (thus the view table) at one price. Of course, if shirts don't do well, I don't want to dump more money into shirts, so the product type has considerable weight.
The second ad is a different kind of ad that is sort of descriptive (ie: come shirts!!!!), once again, the charge is per View, but at a different price than the previous ad.
*No, I do not sell clothes.
If I was to create a table of each ad to be descriptive, it may look like this:
AdSense(adId, adName, productId, productName, productType, date, amount-of-view, amount-of-click, price of ad)
bannerAd(adId, adName, productType, date, amount-of-views, amount-of-click, price of ad)
note that the banner ad is not doing any specific product, just a product type.
The next thing I need to collect is the customers. This part is easy:
Customer(custId, custName, etc....)
Now I have to find out what products have been sampled and who collected them:
Sample -->> pId, custId, adId, etc etc etc.
And of course, I have to see if that sample gets converted to sales:
Sales -->>>>
So basically:
SuperRelation(customerId, customerName, productId, productName, productType, adId, adName, adType, avertisingWhat?)))))))
There's some extra information I have to take into consideration:
I can pull ads for a few days and put them back up at will, thus if Tuesday is a consistent money loser, then no need to advertise on that day. Do products trend in even slopes or do they spike up and down?
All these things are the foundation of the program. I'll go ahead and find a few things to read on building a database. That database class teaches a BCNF algorithm and I'll try to implement that the best I can and then update this thread.
Yes, I fully accept the fact that this program could end up sucking something horrid.
Factor in the fact that this is the most significant program I ever wrote and the fact that I don't have 40 hours/wk to write this, and well....
The truly important part is not only for my own analysis. Hell, I could stare at number all day, but for other people to look at the data and understand it: thus lots of pretty pictures make management happy.