hi doug, i just finished studying that stuff not too long ago...and you've got the right idea. you first examine the business objects you'll be using. since you'll be selling a product, you'll have orders. each order will have one customer, but a customer can have many orders. each order can include many items (products)and an item can be associated with many orders. since you want to create one-to-many relationships in your database, you'll need to create an association table for business objects with many-to-many relationships, like orders and items. remember the primary key of the 'one' side of the one-to-many relationship goes into the table on the 'many' side of the relationship as a foreign key. the association table you create to handle many-to-many relationships will combine the primary keys of each of the 'many' tables to form its own primary key. this synopsis scratches the surface of an important relational database concept known as 'normalization' in case you want to dig further.
hth, dina ----- Original Message ----- From: charlie arehart To: SQL Sent: Tuesday, January 15, 2002 8:48 PM Subject: RE: Database configuration ? Jeffry offers a succinct and useful response. Frankly, the nature of the question suggests that you'd be really well-served by doing a little reading on relational database design. Then this sort of question becomes old hat. :-) There are many excellent titles. Just do a search on "database design" at amazon and look at its most popular choices. Most are uniformly good (because the concepts are fundamentals which all DB designers should know). Some definitely take a lighter approach and are very approachable (and not going to be boring.) It's always tempting to jump right in, or lean on folks to help, but this is stuff you'll constantly bang your head against until you learn those fundamentals. You'll also save yourself a LOT of headache by designing things right up front. It's a nightmare to change things later (both the DB design and the code). /charlie -----Original Message----- From: Jeffry Houser [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 7:04 PM To: SQL Subject: Re: Database configuration ? : hmm: Well, there are way too many variations to tell you specifically what to do, but... I would do something like this: Artist (ArtistID, Artistinfo) Song (SongID, SongInfo) Disc (DiscID, DiscInfo) DiscSongArtist (DiscID, SongID, ArtistID) ( This is an intersection table and will give you the songs on a disc and the relevant artist.... for example, both Madonna and Don McClean do a version of American Pie.. you will want your system to be able to handle that without problems. Also it is not uncommon for a single song to be on multiple CDs, for example anything that was released as a single is usually on a full length album and the single ) As far as ordering stuff, yes I would separate price into it's own table. Are the prices going to change on a routine basis? If so, then when an order is created, I would also store the current price of the item in the order tables, as every time a price changes you aren't changing past orders in the process. I am wondering what site you are working on. At 02:28 PM 01/15/2002 -0800, you wrote: >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 > >Archives: http://www.mail-archive.com/[email protected]/ >Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebarsts -- Jeffry Houser | mailto:[EMAIL PROTECTED] AIM: Reboog711 | ICQ: 5246969 | Fax / Phone: 860-223-7946 -- DotComIt: Database Driven Web Data My Book: Instant ColdFusion 5 | http://www.instantcoldfusion.com My New Book: ColdFusion: A Beginner's Guide February 2002 -- Far Cry Fly, Alternative Folk Rock http://www.farcryfly.com | http://www.mp3.com/FarCryFly ______________________________________________________________________ Macromedia ColdFusion 5 Training from the Source Step by Step ColdFusion http://www.amazon.com/exec/obidos/ASIN/0201758474/houseoffusion Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
