If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Access Database for buying/selling/loaning used games.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2007
Posts: 1
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.

Last edited by Brotherhood; 08-20-07 at 00:46.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: New York State
Posts: 1,177
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.

Hope my musings are helpful.

Sam
Reply With Quote
  #3 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
Quote:
Originally Posted by Sam Landy
use a contact table for the demographics (ContactID, flag (C-ustomer, V-endor, or B-oth), last name, first, email, ad infinitum)
I disagree.
Contacts(ContactID, Surname, Firstname, TelephoneNumber, email, etc)
Vendor(VendorID, VendorName, TelephoneNumber, location, etc)

These are two separate entities that do not necessarily share the same attributes - therefore needs to be modelled to reflect this.
__________________
George
Home | Blog
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 1
hello i believe that this is an old post
but i have huge database to sell let me know if u r interested
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On