I'm building a program for my own uses at work. It is going to be programmed in Python3.

The idea is to create a DB or XML of the products that we offer through an online outlet so I can track how many samples we send out and how much we spend on advertising on each product.

The quantity of each sample is ~usually~ one. There are a few exceptions to this rule, but I don't think that over-all, those edge cases need to be considered: more than one usually translates to different colors, so effectively, one sample of ROYGBIV will be marked as one.

I want this part to be broken down into:

In XML, I have:

<month>Oct2011</month>
<sample>
<pId>11111</pId>
<pType>t-shirt</pType>
<?></?>
</sample>
....
....
...
...
</month>

I only care about the months, not that dates, but the problem is compounded.

I also need to track how many times each item we are advertising was viewed and weigh the samples against how many times they were converted to actual sales, track how much money was spend on advertising each product (the samples are NOT correlated to advertising by nature), etc.

The next issue is that once I can make this part profitable for the company, I will probably have to do this for another outlet, at which point I can compare trends and profit, etc.

I'm fairly confident that I know what data matters and what data doesn't matter, so extensibility won't be a huge issue (though this is probably not the truth, lol).

Since I'd be the only one using this, mostly via command line, it should be set up for what will be most convenient for me.

So, for something like this, do you think that XML or SQL is better, and does it really matter?

I'm confused, where is the data going to be stored? If it is in a db isn't it going to have to get converted to sql at some point anyway? Or are their dbs that will aggregate xml files into data stores?

database!

100% database AINEC.

Yeah, more I though about the Schemas involved, the more I side with DBs. I guess it doesn't matter whether I use Postgre or MySQL. Python3 has modules for either one.

On monday, I'll draw up a bunch of schemas and try to think which is best. I was originally thinking of doing a table for each month, but that seems a little too bloated, but then again, doing one for each advertising module would seem very disorganized:

create XXXXX (ID char(5), type char(x), month char(x), quantity integer, etc...)

will probably be okay to start with. I wish I had the foresight to see everything I need. As long as I can compute ROI and create pretty graphs...

If you're doing anything with dates, Postgres so much easier to manipulate.

do not make a table for each month, (or for each "module" probably). month should be a proper datetime/whatever. these things sound like they should be columns.

post more details. or just have a go at a schema. as you try to sketch things out, you can do example queries in your mind and see if they are possible / figure out a better understanding of how you could implement the tables.

I'm thinking from what you say so far you should have a table of "products", "customers", and a middle table "samples" where products are given to customers. and another one "sales" where customers actually buy products. Filled with data that gets you a lot of pretty info, what products generate sales from samples, what are duds, who are your good customers and who's just a sample whore etc.

Interestingly FWIW Google uses a flat file database (which is sort of similar to what you are proposing with XML) but to go down that route is really really specialised and for 99.999999% of people sticking to a database if it is available is always the right choice.

I think a DB is a bit overkill for something like this. I am guessing that you are not going to be running any complex queries so XML should be fine. Personally, I would actually store the data in JSON objects.

I think this is a good case of not overcomplicating things... reading the OP, a database is really not required for what you want to do (and becomes a bit disadvantage as far as portability goes).

This is what I have so far, which I think creates a *somewhat* solid case for a database. Not sure how I feel about abstracting away the date fields
:
The other issue is the Ad Table. There are (so far) two types of ads going. At first, there were two ad tables but I decided that I would be okay with having empty cells in this table. Expanding it to an extra column won't be hard at all, so there didn't seem to be any benefit to creating extra tables.
================

Date(dId, monthYear, date, day)

Ad(adId char, adType, adName, pId, dId)

Products(pId, pName, pType)

Customer(cId char, cName char)

Cost(costId, dId, adId, costDollar float)

DailyViews(vId, adId, dId, quantity)

DailyClicks(clickId, adId, dId, quantity)

Samples(sId char, pId, adId, cId char, dId)

Order(oId, cId, sId, pId, dId, saleDollar float)

======================

Basically, I'll use something like this as my base, then somewhere in the lower levels of the Python program will be repository of common queries so I only have to type in the dates.
Also, to make sure the build-up is as easy as possible, I'll include some prompts in the program, so:
>> pick a database: Date (d), Ad (a), etc etc:
>> fill in the dateId:
>> fill in the monthYear:
.....
I don't have a ton of time at the moment, so I'll run some imaginary queries over this later on and think of what will happen. I basically want to see if ad is profitable and a few other markers like that.

quite a bit to say, but i'll start with:
omgNO

elaborate more on clicks / views? web stuff?

I always store dates as a timestamp. Makes comparison much easier.

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.

so this works like: Customer clicks on a google adwords ad, gets taken to a page where they request a sample (conversion #1). then samples are sent, hopefully customer buys product (Conversion #2) ?

If you're actually running adwords campaigns, strongly suggest you install prosper202 to track / optimize them (If this is your responsibility). Then build the database yourself for traking the samples -> Sales conversion process.

I wish I was running actual AdSense / p.p.c. since I am sure the modules would already be there. It seems similar, but it's not quite the same.

Yes, this is my responsibility, unfortunately.

prosper will work for any traffic, no problems. It might not really be too useful if you're only running two ads on one site, but if you're running ads through a network thus have got traffic coming from all the net it's great for reporting what works well / not so much.

Am I otherwise correct about the sales method?

You need a website for Prosper, and this particular thing is not at all dealing with our website. Think of this as a huge swap meet, where the potential customers are viewing a bunch of products and ours is one of many on the virtual shelf.

Think of it as Amazon, but what it would be like if you were a seller and you were restricted from the buyer interface, thus you had little idea what Amazon actually looks like or acts like.

It sounds boring without a lot of explanation, but it's actually quite fascinating. At this point, I don't know what the customers are looking for, what they are sampling, nor do I know what the competition is advertising, so it's all blind number analysis. Hate to beat a dead cliche, but it's just like playing poker since it is mostly random and I have no clue what my opponents are holding.

If you pay attention and crunch numbers, quite a bit of story starts to reveal itself. For instance, I am starting to get a sense of what the competition is doing and what the customers are looking for, but I don't have enough data to call one idea profitable or another a dog, so I am doing a bunch of somewhat blind assumptions and going from there.

So, the db as it stands right now (not quite, some information has to be restricted):

ProductViews(date, pId, pViews);
Products(pId, pName, pType);
Customer(cId, cName);
Samples(date, cId, pId);
InfoViews(date, sViews)

Ad(adId, date, pId, shown, viewProducts, requestSample, clicks, cost);
Ad(adId, date, adType, shown, clicks, cost);

Still need to add the conversion table and then woohoo! build the first layer of Python and get all the information I currently have inserted.