Advice on Table Structure

[silicontoadet]

Hi,

I'm new to mysql and databases in general, I've created two tables, one for "user" and one for "tracks" lets say:

user(userID, name, email)

track(trackID, name)

Let's say I have 100 users and each user buy's 1000 tracks, at the moment my user table is like so:

user(userID, name, email, tracks)

where tracks is a comma separated list of id's. Something just doesn't feel right about this, any suggestions for the best approach on this.

Thanks

Toad

 

[guelphdad]

yes never store comma separated lists. do a search on DATABASE NORMALIZATION so you understand the concept.

You need three tables, the first two you have user and track at the top of your post.

you then have another

usertracks
userid, trackid

and store a new row for each track a user owns.

you then join the three tables to output the tracks each user owns.

 

[johnwm]

For further reading try There is a good (very basic) article on database design at

http://r937.com/relational.html

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[silicontoadet]

Thanks guelphdad - very helpful,

Thanks johnwm - will have a read