[ic] Advice needed for IC/eBay API integration

[Grant]

> Grant <listbox at email.com> wrote:
> > I'm now very close to being finished with the IC/eBay API integration.
> > eBay's developer testing site was broken for about 10 days or so, but
> > it started working again a couple days ago and I should be done very
> > soon.  The integration will consist of something like this:
> >
> > - 5 UserTags
> > - 7 jobs files
> > - 7 jobs directives
> > - 1 new page
> > - 1 new variable table entry
> > - new fields in transactions and inventory tables
> > - new ebay table
> > - log_transaction modifications
> >
> > It will be going up at Kevin Walsh's site ( www.interchange.rtfm.info
> > ) along with detailed info on how it all works and how to use it.  Of
> > course, I will post here when I have it up.
> >
> > So I guess that was an update.
> >
> > Anyway, I'm facing an important decision in the design of the whole
> > thing, and I'd really appreciate some advice on it.  Basically, I
> > could change around the way it works and make it quite a bit cleaner
> > and more extensible, but it would require adding every item listed on
> > eBay as an entry in the new ebay table.  That could really get to be
> > a lot of entries that aren't being used for anything unless the item
> > ends up being sold.  Should I:
> >
> > a) not write every listed item as an entry in the ebay table and
> > sacrifice some cleanliness and extensibility
> > b) write every listed item as an entry in the ebay table, but delete
> > each record that isn't needed after the listing has ended
> > c) write every listed item as an entry in the ebay table and not
> > worry about the bloated database
> >
> > I'm leaning toward b.  If you agree, how can a record be deleted from
> > a table without going through the UI?
>
>
> C is better, because every item, even if it sells, is an
> accountable item. So,
> there will be ebay list fees (possibly optional listing fees like
> BOLD, etc),
> and maybe even fees for using YOUR app, regardless if the item sells.
>
> Even if you add 500 items a week, that is only 6000 a year.
> depending on how
> you have the accounting setup, that means maybe at most 25,000
> entries a year,
> which isn't bad, especially if you placing 500 items a week on ebay.
>
> I would let the users flag an item for deletion, depending on
> whether or not
> they want to delete it. If you app is charging fees, they
> shouldn't be able to
> delete it obviously, as it would cause you more customer service headaches
> later if they cannot go back and see their account history, with
> associated
> item (visual proof you are not charging them for bogus items). I
> am guessing
> your app hosts the images as well yes? I would have those deleted
> every once in
> a while, based on corresponding item rows end_date.
>
> If you will let them delete, have them flag it, then run through
> with a weekly
> cron to delete all rows flagged (and respective rows in other tables if
> necessary). JOBS sounds like a timed run of ITL, which is
> probably even easier.
>
> Of course without an overview of what you are doing with the
> tables, what their
> part is in the grand schema, and how this app should work, any
> advice will be
> general advice.
>
> HTH
> Paul

Paul, thanks for your response.  I should have included more info on how the
app works in my initial post.

The app will do the following, but any of the mentioned specifics can be
easily changed:

- list regular eBay listings:
Every 3 days, the top 50 best-selling (based on existing "Generate others
who..." functionality) qualified items will be listed as single-quantity,
3-day, fixed-price listings.

- list eBay Store listings:
Every hour, any products not currently listed in your eBay Store will be
listed as "Good-Til-Canceled" listings.

- associate orders with auctions:
Auction winners are directed to your auction page where they can enter their
auction number(s) and eBay user ID, and have their shopping cart populated
with any items they purchased from your eBay listings.  They can then
continue shopping or checkout right away, and the IC order is placed as
normal, except it is associated with the auction number(s) they entered.

- leave positive feedback on eBay for transactions in which the item has
been shipped:
Every day, positive feedback will be left for eBay transactions in which the
purchased items have been shipped to the buyer.

- relist ended and unsold item listings:
Every 3 days, any ended and unsold listings will attempt to be relisted.
eBay's rules say unsold items can only be relisted once, and are not subject
to the insertion fee.

- send email notifications to the buyer (payment instructions, payment
reminder):
Every hour, emails are sent to any auction winners (once each) directing
them to your auction page, and reminders are sent to auction winners that
haven't placed their order through your IC store within 7 days of the
auction's end (once each).

- send email notifications to you (submit a Non-Paying Bidder form to eBay,
submit a Final Value Fee Credit form to eBay):
The submission of the Non-Paying Bidder and Final Value Fee Credit forms to
eBay must be done manually so an email is sent to you with the necessary
information and a link to the appropriate page on the eBay site.  NPB emails
are sent 14 days after an auction's end if necessary, and FVFC emails are
sent 21 days after an auction's end if necessary.

The focus is on automation.  Off the top of my head, the only input that is
required from you after setting it up is changing an order's status to
"shipped" to trigger the positive feedback when the IC job comes along.  I
think most people do that anyway.  I used a much more manual-type system to
manage eBay listings with IC in the same way for many months before I dug
into this project.  All bugs in the general design of the system have been
ironed out.

This will all be donated to the IC community.

Now what do you think about writing all listed items to a database?  If you
think that should be done, should unsold item records be deleted once their
use is up?  If so, how can that be done in IC using a DBM database?

- Grant