Hi all, I have a few questions in relation to a database intended for a company that sells antiques for commission on EBay, acquiring them from the public. If you have the patience to read them and even answer them, that would be fantastic.
[1] Part of this database would require a CLIENTS table, which would list the contact details for people from whom antiques were acquired. I had orginally intended to link this, via *ConsignmentID*, to a CONSIGNMENT table, which would incorporate different consignment rates for different categories (e.g trade, personal, public etc). But sometimes individual repeat clients have special consignment rates permanently allocated. For example, Bob Smith from the antique trade might have a consignment rate of 10% because he is such a frequent customer. Would it then be best to link from CLIENTS to CONSIGNMENT via *ClientID*, or via *ConsignmentID*? The complicating factor is that many clients are simply one-offs, and therefore would just have a general public consignment rate. [2] Each individual listing has a different postage rate according to the destination. For example, each listing will say that US postage is $15.00, AU postage is $5.00, etc. Sometimes the currency is listed as US dollars (depending on whether it is listed on the US site) and sometimes in AU, for Australia-orientated items. I had intended to have the following structure for listings: ITEMS table links to LISTINGS via *ItemID* LISTINGS table links to SALES via *EbayItemNo* How would you suggest structuring the postage aspect (needed for record-keeping), especially with the currency issue? I had intended to have a field in LISTINGS called *Currency*, to select AU or US. Should I then use a *PostageID* field to link to a POSTAGE table? Or should I just include those fields in the LISTINGS table? (This would cater only for quoted postage, but a different approach would be needed for actual postage costs once the item has been sold). [3] There are various calculations that need to occur post-sale. I have the following tables linked: LISTINGS table, to SALES (via *EbayItemNo*) SALES table to BUYERS (via *BuyerID*) SALES includes a *FinalPrice* field. The calculations that need to happen include: a) Calculating GST payable based on the *FinalPrice* field in SALES (e.g SALES.*FinalPrice*/11) b) Calculating international insurance (where applicable) based on SALES.*FinalPrice* and BUYERS.*Country* (where insurance is $7.50 for the first $100 of value, and $2 for every $100 increase thereafter) c) Calculating ebay fees, based on: - Insertion fees - Gallery fees - Reserve Price fees - Final Value fees - PayPal fees d) Calculating various commission amounts, including: gross commission, commission payable to client (less consignment rate), gross commission to company (based on consignment amount less ebay fees). What would be the best approach for such calculations? Should I create a new table for this kind of data, or use fields? If so, into which of the following tables would I be best to insert the fields? CLIENTS CONSIGNMENT ITEMS ITEM_CATEGORIES LISTINGS SALES BUYERS POSTAGE Any suggestions/redevelopment ideas would be much appreciated. Thank you! ------------------------ Yahoo! Groups Sponsor --------------------~--> Get fast access to your favorite Yahoo! Groups. Make Yahoo! your home page http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
