I am working on a project for uni (an ecommerce site), but am stuck designing the database. It is a music store, so I know it needs a customer, products and order table. But then I don't know what to do next. I've done this before, but I can't remember. I've looked in a couple of books and online, but it hasn't helped.

For example the table products(product_id, artist, album, song_title) is in 1NF, but what do I do to put it in 2NF, then even 3NF if needed.

If someone knows how to do this, could you talk me through what tables I will need.

Cheers

Here is an article about database normalization: http://databases.about.com/od/specificproducts/a/normalization.htm

However, just a quick look at what you proposed shows some weaknesses. Are you selling individual songs or albums? This will make a difference. For the sake of argument let's suppose you are selling full albums.

you PRODUCT table should contain (at a minimum) a unique product ID, album name, and ONLY other information that is SPECIFIC to each album. You do not want to include artist or songs - I'll explain why.

An album can contain many songs AND some songs can appear on several albums by different artists. So you would want to have a table for songs that includes song ID and song name. Then you create a cross reference table that ties songs to the albums with columns for songID and albumID. This makes it easy to do a search for all albums with a particular song.

The same logic works for artists. many albums can belong to a single artist and sometimes artists will work on an album together. So you need to have a separate table for artists and then cross reference them with the albums.

Michael J