Best Way for Parsing?

tonyr1988

I want to do a website where people can upload XML files from their iTunes song lists, and other people can view them.

I have successfully parsed the XML file in PHP and gotten the variables that I need. I have the XML file stored on my server first (renamed as [username].XML), and then parsed. Here's my problem: should I:

1) Parse the XML files and store the variables in my MySQL database, or
2) When someone goes to [username]'s page, parse the [username].XML file and echo the variables?

#2 would use less space, but #1 would give me more features, right? If I go with #1, I would be able to make the database searchable and figure out how many songs there are.

What would you all do?

Furthermore, if I go ahead and store the variables into my MySQL database, how should I do it? Should I have one table named "songs" with fields of:

username
artist
album
etc.

Or should there be a ton of tables named [username] with fields of just:

artist
album
etc.

Does that make sense?

jheitz

I would definetely go for option 1, no doubt, for the following reasons:

1) Speed of access - You only need to present the data, no need to do real-time on request parsing which, if your site is successfull and many people access it, it will consume many resources anyways (the parsing in option #2).

2) Unified view - As you've mentioned, you are able to make all kind of analysis once the data is structured in a DB.

3) Contrary to your belief, storing in the database will, in the long run take less space. The reason is that you will not be storing all the XML headers in the database, so you will need less bytes to represent the user's file

As per the db structure, don't even consider creating a table per user, you don't want to be modifying the database schema (ie: tables) on the fly... that would be a real nightmare.

You need tables such as:

USER
SONG

at least, but you could even go to a further step and have other tables such as

ARTIST
etc...

Hope this helped

tonyr1988

I'm slightly confused as to what the format of my table(s) should be.

If I did different tables for user, song, artist, etc....how would I associate a particular user with a song on their list? The way I have it now is:

Table 1: contains user information, such as username, userid (a number), e-mail, etc.

Table 2: the actual playlists. This table has the fields of:

user_id
artist
song
album
etc....

It's simple - is that the best way? I'm just worried that it would take forever to search through all those entries (one per song for everyone's playlists). Granted, when doing any SELECTs, I always have a WHERE user_id = ???.

Is there a better way, or is that the most optimized?

jheitz

The best way would probably be to have a USER, ARTIST and ALBUM tables. Similar to your approach but on the PLAYLIST table have an artist_id and an album_id that are Foreign Key's to the ARTIST and ALBUM tables (just like user_id)

I don't understand what you mean by saying it would take forever to search through all those entries... What do you mean exactly?