Grace- You have over-normalized in some cases. Here's how I would do it:
Categories: CategoryID (P), CategoryTitle, CategorySortNo Prints: PrintID (P), PrintTitle, DateTaken, PrintDetails PrintCategories: PrintID (P1), CategoryID (P2) Listings: EBayItemNo (P), PrintID (F), DateListed, SizeID (F), etc. I might include the FinalValueFee here rather than in Sales. Do you need a QuantityOffered field here? Buyers: EBayUserName (P), FirstName, Surname, EmailAddress, Country (Don't use "name" as a field name - that's a reserved word.) Sales: SaleID (P), EBayItemNo (F), EBayUserName (F), SaleDate, Quantity Invoices: InvoiceID (P), EBayUserName (F), ShippingName, ShippingAddress, DateShipped, ShippingWeight, SpecialRequests, Insurance, Tax, TotalPrice, DatePaid, PaymentMethod, PostageID (F) SalesInvoices: SaleID (P1), InvoiceID (P2) Notice that payments get logged into Invoices. For most items, you'll have one Sales record, one Invoices record, and one SalesInvoices record. But this structure also allows you to group multiple sales into one invoice and collect one payment for the entire lot. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of grace_smith8788 Sent: Wednesday, November 23, 2005 11:54 PM To: [email protected] Subject: [ms_access] More questions about EBay sales database Attn: John Viescas Hi John, I've been developing a portion of the EBay database I wrote about the other day (see EBay Sales.mdb in the file list), and I was hoping that if you had the time, you could have a look at it and suggest improvements for the structure. I imagine you would get a sense of what I want from the database, based on the tables/relationships so far. My major problem is determining the right connections between SALES, POSTAGE and PAYMENT. As you can see, I have created two transaction tables (LISTINGSALES and LISTINGPOSTAGE), to form many-to-many relationships. But I am not wonderful with logical/lateral thinking, and I can't quite figure out what's best. My problems are as follows: [1] Because a buyer can purchase an item from more than one listing at once, I need a way to be able to enter (in a sub-form format, I imagine) the multiple items they purchased, if applicable. I still need to recognise and record individual sales stemming from one listing (thus the SALES table), but I need a way to ensure that if a buyer buys three different items from three different listings on the same day (or within a short space of time), I can total the amounts payable, and ship them together. I created a LISTINGSALES table for this purpose, but have I set it up appropriately? Can you suggest a better way? Or would it be best to set up a separate SHIPPING table, to cover details like ShippingDate, ShippingAddress, ShippingWeight, etc? [2] I wanted to create a separate table (PAYMENTS) to record things like payment method, amount received and date received, for each individual sale. I can't actually figure out where the link to PAYMENTS(PaymentID) would go; into LISTINGSALES or SALES? This comes back to the original point, that I'm unsure if my current structure is working. At the moment, when I try and link PAYMENTS(PaymentID) to LISTINGSALES(PaymentID), it tells me that it will be a one-to-one relationship, whereas I imagine it should be many-to-many. [3] LISTINGPOSTAGE was set up so that multiple postage methods could be recorded on one listing. (NB: This database deals only with the shipment of photographs, and therefore weight of parcels isn't really an issue). For now, the purpose of the exercise is for me to select the right postage method for charging shipment - e.g if the buyer lives in Germany, then I would select the record that dealt with European postage. Given this, is this table really necessary? ACTUALPOSTAGE deals with raw postage costs, whereas POSTAGE deals with the costs charged to the buyers. The fields PostageCostAUD, PostageCostUSD, etc allows for different currency types according to the PostageID field (i.e an example PostageID would be AUSTReg, for Australian Regular post; or USOAEco, for Economy post to the US). So, if the value of one record's PostageID field was USOAxxx, then only the PostageCostUSD field would be filled. Is there a more suitable way to do this? I really appreciate your help, thank you so much. ------------------------ 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 ------------------------ 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/
