Access Database for buying/selling/loaning used games.
My brief/what I do is:
I buy used collectable/valuable video games and sell them on for profit as well as renting them out. I own multiple copies of various games and each one is unique and I keep track of them. They have different states of wear and different statuses i.e. some are for sale, some are on loan, some are just in my collection. I want an archive of old games I have sold too so another status feasibly would be 'sold'.
e.g. I own four copies of the original PAL version of Ico on the PS2. Two are in excellent condition and both for sale. Two are in good condition; one of these is on sale and one is on loan. I have owned a good and an excellent copy previously and sold them on.
I buy games from Ebay and Amazon sellers as well as high street stores. I also sell back to these sellers and stores. e.g. I could buy a copy of a game from my local game store making them a vendor, but I could also sell another copy of a different game to the same store making them a customer.
I rent copies of games out to my friends and acquaintances making them customers. I also buy and sell games from/to the same people making them vendors and customers.
I need to keep track of what I own, what I've sold (archive) and what I've loaned out. I need to keep track of purchase and sale prices of copies to track profit.
I need to model this.
GAME table contains game data. PK = UPC (Product Code)
GAME_COPY table looks up game data via game UPC. Each copy has its own unique PK.
Because vendors can be sellers and sellers vendors do I need two seperate tables (VENDOR, SELLER) or can I just use a single CONTACT table?
SALE, PURCHASE tables with date, price, contact for each one? Or TRANSACTION table, with look-up of transaction type (sale, purchase), date, price, contact. Will this work when I want to display both sale and purchase data for a single copy of a game separately? Because then a copy will be involved in two separate transactions. Would sticking with separate purchase and sale tables for each copy work better?
RENTAL table with game, copy, contact, hire date, due date and hire cost.
Is that a viable model? How would others model this? *scratches head* Any help would be much appreciated.
I've never done this, but often thought about it. Other software packages madeningly disallow an entity to be both vendor and customer, although my company had several trading partners who were indeed both.
My approach was always to use as few tables as necessary, but don't waste the field space; it's expensive.
In other words, since, for example, a customer has few parameters different from a vendor, use a contact table for the demographics (ContactID, flag (C-ustomer, V-endor, or B-oth), last name, first, email, ad infinitum) but use a separate table for the financials, maybe called VStats and CStats (the same ContactID, date of last sale - or date of last order, etc.).
Purchases and sales, on the other hand, should probably be in separate tables, because the parameters are totally different. If you combined these tables, you would only use half the fields for any transaction, while the remaining fields would remain null - that's what I meant before about not wasting the field space.
The inventory table, of course, would be totally separate. You would probably want to have a "disposition" field (or something similar) which might read "sold", "loaned", or even "destroyed". You would have to allow for buying back a sold item, and getting back a loaner. All in all, a tantalizing database. Good luck with it.