Dear Peter,

Thank you for the thoughtful note.

Your last paragraph makes a distinction between my app and the more common situation. In many FoxPro apps, the data is generated within the app, so the data can be structured from the beginning. In my case, I am grabbing data from the outside world, and trying to impose a normalized structure upon it.

As programmers, it is easy for us to say “The ebay people should have set it up differently, they should have done this …” However, I think the ebay people have done a good job of letting the sellers do whatever THEY want, within the constraints of the ebay application. (The sellers are the ones who pay ebay.)

Why am I doing this? I want to track price trends on old junk. I am not buying or selling right now, but when I get there, I will have records of prior transactions. I want to impose a normalized structure on those records so I can conveniently and efficiently access the data.

From the perspective of people interested in old junk (like me), ebay changed the world. ebay has allowed buyers and sellers to find each other more efficiently than had been possible before. As a result, the price of many things have gone up, while the price of some things have gone down. The mental database which I acquired BEFORE ebay was no longer valid.

I would want to access the data in this way. Suppose I am considering bidding on an item. So I would want to consider prior sales of similar items. I select the search criteria, and pull up a list of the prior sales. Etc.

Inside the tables, I use internal keys. ebay assigns an item number, but ebay may reuse them at some point in the future. You may be right that the PK would be ItemNumber+PossiblyMore+EndDateTime, but I keep the key shorter (and index files smaller) by generating a more compact key. I use all the characters (except the ones that cause problems for FoxPro forms), so a 5-character key gives about 10^12 (248^5) possible unique keys, while the 10-digit ebay item number only allows 10^10 (as they only use the 10 number characters). I first record information on items by scanning the lists of either completed or pending auctions. At that time, each item either gets a new key or a recycled key (that is, recycled from an item that I decided not to keep).

I disagree with you (I think) on one point: I believe the PK for ebay is the item number PLUS the auction end date/time. ebay may reuse item numbers years from now, but they have not done so yet as far as I know. So in the short term, the PK for ebay is just the item number.

I use shorter keys for the model key (3 characters), brand key (2 characters) and type key (2 characters). So the total length of all the key fields in the ItemHits table is just 12 characters.

The purpose of my effort is not to force normalization on the people who work for ebay or on the people who buy and sell on ebay. Rather, I just want to grab all the data that interests me, and give myself the means to access it in the way I want. On the last point, normalization should help.

The solution to my problem may be for me to learn to use SQL better (see the other reply to my original message and my response).

Thanks again,

Rick