MySQL - photo comments, votes, keywords, ++, in single table?

!!!Albert-0-

New Member
I would like to move my photo management system from XML to MySQL and extend functionality of my current system.Basically, I have few things in MySQL and few in xml files. The situation right now looks like this:
  • "news.xml" - single file:
    there are stored all news on my site (new uploads, votes and coments) and the structure of xml is:\[code\]<all_news> <photos> <image image_name="photo1.jpg" path="my_photos/" date="12.12.12" info="just Uploaded!" /> </photos> <comments> <image image_name="photo1.jpg" path="my_photos/" date="12.12.12" info="just Commented!" /> </comments> <votes> <image image_name="photo1.jpg" path="my_photos/" date="12.12.12" info="just Voted!" /> </votes></all_news>\[/code\]
  • "photo1.xml", "photo2.xml", "photo3.xml" ... - multiple files:
    These files are created dynamically if someone leaves comment on my photo. For example if someone comment my photo, php script automatically create "photo_name.xml" file in format:\[code\]<comments> <comment sender='John' text='nice photo!' who='user' date='22.12.12'/></comments>\[/code\]
  • "Votes" - MySQL table:
    there are stored all votes, and the structure of the table:
    • \[code\]id: "photo1.jpg"\[/code\]
    • \[code\]vote: 5\[/code\]
    • \[code\]uid: "IP voting person"\[/code\]
Now I would like to merge all these things into a single mySQL database table, so each time when photo is loaded - pull these photo informations from single DB table (instead of loading few xml's and DB in same time)... So my question is:
What table structure is the best in this situation for easiest working on (pull data, move, delete, edit, add, sort...)?
Should I keep all things for single file in single row using unique id = "photo.jpg" and multidimensional arrays? Eg:\[code\]uid: photo_name.jpgpath: my_photos/isNewPhoto: array[true, array[info:"just uploaded!"][date:"12.12.12"]] (multi-dimensional array ??)isNewComment: array[true, array[info:"just commented!"][date:"12.12.12"]] (multi-dimensional array ??)isNewVote: array[true, array[info:"just Voted!"][date:"12.12.12"]] (multi-dimensional array ??)votes: 1,5,3,2votesIP: 123123123,123123123,123123123comments: array[array[sender][comment][date][who]]] (multi-dimensional array ??)\[/code\]
 
Back
Top