Welcome to WebmasterWorld Guest from 35.175.191.168

Forum Moderators: open

Message Too Old, No Replies

Submitting multiple lines but using most of the same data

     
3:00 am on Jun 26, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:May 7, 2008
posts:45
votes: 0


I have a website that small bands can use to submit music to in order to share. In the submission page you fill out a form for artist, album name, etc. and tracks. Now what I want is for each track that is submitted to be on it's own line in the table. This will allow for much easier searching of the database and organization. Each track line will also be accompanied with the artist and album. Here is an example of what the table would look like with 3 lines filled in (2 of the line are from the same album and were sent in at the same time):

Albums ¦ trackname ¦ albumname ¦ artistname
1 ¦ Fix ¦ Wisconsin Death Trip ¦ Static-X
2 ¦ I Am ¦ Wisconsin Death Trip ¦ Static-X
3 ¦ Beverly Hills ¦ Make Believe ¦ Weezer

P.S. Albums is just the primary key

Thanks in advance!

3:45 am on June 26, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Not sure what you're asking, but search around here for normalization.

Your current structure will have the same data over and over - this is a waste and if you want quicker queries, you would be better off to store those in different tables. Example: 1 artist, three albums, 12 tracks per album, this means you'll be storing the artist name 36 times, the album names 12 times each.

Here's a better start that moves toward normalization:

artists
id ¦ artist_id ¦ artist_title ¦ other_data ¦comments

albums
id ¦ artist_id ¦ album_id ¦ release_date ¦ formats ¦ comments

tracks
id ¦ album_id ¦length ¦ comments

Your select statements become a little more complex, requiring joins, but your database is smaller and normalized, and you have a more robust basis for various types of searches.

There are a couple important points here, one you'll probably spot right off, the other, maybe not.

Note for each table I have "id," an auto-increment field, and a unique identifier field (artist_id, example.) Many programmers use the id field to do their joins - including myself, . . .sometimes . . . but it's not the best idea. If the data needs to be re-imported, those auto increment fields may change. A unique id you generate will not.

A positive aspect of normalizing your tables is it allows you to do searches on integer fields, which are always faster than searching text fields:

select * from tracks where album_id=12345;

4:10 am on June 26, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:May 7, 2008
posts:45
votes: 0


Thanks for the fast reply,

Well I understand what your saying about speed and such but due the fact I am not that advanced with mysql and php I think I would like to stick with what I have.

What I want to happen is for every filled out "track" input I want a line posted into a mysql table with the artists name, track name, and album name.