Ok here's how I would do it. 1 table for artist- a reference table 1 table for album names. Use the artistID to hook to artist table 1 table for any track names. This gives you a couple of one to many/many to many relationships One artist=many albums One album (or many albums)=many tracks (think about remakes here- it would only need to be entered once). 1 table that hooks these all together: Artist Id/AlbumID/TrackID
Then you of course will have customer table/orders table. You can't assume that the price will only be changed once- so you can do a couple of things. 1. Leave the price on the album table or make a reference table for prices. When the person buys a cd the actual price is put on the the orders table. That transfers the actual price (through a query) and allows you to change the price easily in the album table. If the company needs to track what albums they marked down/when/how much marked down- you can make a history table. The info would be sent to the history table albumid/priceid/date then the new price takes the place of the old price. 2. Use a dynamic percentage equation. You still track the retail price then have a (dynamic) field for discount %. (% of discount- default being no discount). You still need the history table to say when/howmuch/what cd was marked down. So after all my rambling it boils down to you need some type of history table- no matter what. along with albumid/price(id)/date you may want to consider things like is this a permanent markdown/temp markdown. Good luck,j -----Original Message----- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 5:25 PM To: CF-Talk Subject: Re: SQL? So basically I should have say an inventory table.Then create a relationship with the album table IE [inventory] ID album_ID album_name cost saleprice retail taxable etc etc etc There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown ----- Original Message ----- From: "Janine Jakim" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, January 15, 2002 2:00 PM Subject: RE: SQL? > The problem with keeping the price with the album. What if the album > goes on > sale? You'd change the price in the album table then suddenly anyone > who > bought the album previously shows that they paid the sale price. > You would at least need to add a history table or make sure that the > sales > table shows the actual price and isn't just hooked to the album table. > It's been a long day- hope this is coherent enough. > > -----Original Message----- > From: Douglas Brown [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 15, 2002 5:01 PM > To: CF-Talk > Subject: SQL? > > > I am trying to figure out how I should set up a database for an > ecommerce site. I will be selling music CD's. > > Each artist can have several albums, and each album can have several > tracks. I am pretty sure I will need 3 tables for that info. What I am > wondering , is where should I keep pricing info and such? Should it be > in the album table or should there be a seperate table for pricing etc. > > Any insight would be helpful > > > There are two major products that come out of Berkeley: LSD and [Unix] > BSD. We don't believe this to be a coincidence. > > > > Doug Brown > > ______________________________________________________________________ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
