MySQL Table Theory Help

[DJ RRebel]

OK guys ... finally going to make the BIG plunge and start all the hacks I've been waiting for a 'reliable and proven' vB3 before starting!

I feel like that time has arrived!

Anyhow, What I want to do (I have a club music site), is start a new review forum. But I'd like to add seperate fields for:

- Artist Name
- Song Title
- Remix Name
- Release Year
- Record Label
- Genre(s)
- Sub-Genre(s)
- Review (Post Table: pagetext)
- Review Author (Post Table: username)
- Review Date (Post Table: dateline)

?- Some field to relate the REVIEW table to the THREAD table [not sure of this, see below, would be the thread number of the song reviewed]


Also an unfixed (0 for now, but could be 1 or 2 or 3 depending on the artist's submission and alternate download servers) amount of:

- Download Server Icon
- Download Link
- Show Download Link Start Date (not sure yet)
- Show Download Link End Date (not sure yet)

?- Some field to relate to REVIEW or THREAD table.


I want the "Thread" title to be in a format (artist name) song title (remix name YEAR)


Now, considering the size of my forum (20,000+ members, 32,000 threads, 265,000 posts, 25 forums), would it be best for me to just start a new table (or two) and then somehow relate the new table with artist name and the rest to the THREAD table? Or should I just add a few columns to the THREAD table? Would doing it one way or the other slow things down considerably?



I don't really know PHP / MySQL at all, but I do understand the theory and usually can manage to get things right after alot of trial and error .. lol ... anyhow, I think this is what I should do, although PLEASE correct me if I'm wrong, or also let me know if I'm doing it right?


1- Add a 'is_review_forum' column to the FORUM table.


2- Create a new REVIEW table.


3- Create various REVIEW table fields (excluding the ones already covered in the POST table).


4- Create a new DOWNLOAD table.


5- Create various DOWNLOAD table fields.


6.A- Create REVIEW_ID column in the THREAD table to relate/link the thread to the correct review.
OR
6.B- Create THREAD_ID column in the new REVIEW table to relate/link the review to the correct thread.


7.A- Create DOWNLOAD_ID (array?) column in the REVIEW table to list all (if any) DOWNLOAD rows affiliated with the review.
OR
7.B- Create REVIEW_ID column in the DOWNLOAD table to relate/link the download data to the correct review.
OR
7.C- Create THREAD_ID column in the DOWNLOAD table to relate/link the download data to the correct thread.


8- Get into the vB3 PHP coding for new thread and add an IF 'is_review_forum' then show various REVIEW input fields and DOWNLOAD fields (times whatever max number of downloads I think any review could have).

(8.1)- I guess I could also just have a "# of Download Links To Add" drop down field showing [0 to X] so that on the next page have the corresponding number of DOWNLOAD field input boxes if any. But I think that's getting a bit too ahead of myself and complicated ... lol.


9- Remove the Thread title entry box and create the THREAD table's thread_title field on submission using the Artist Name, Song Title, Remix Name and Release Year fields.


10- Tinker with the submission PHP page to store the appropriate data in the appropriate tables (don't know how to do this, but it shouldn't be too hard to figure out I'm hoping, and I can look up that alot easier than the actual theory).


11- Tinker with all the PHP pages that display forum and thread data to get/call the proper data fields if the forum is 'is_review_forum'


12- Tinker with whatever template I want to show the new data.


13- Pray that I don't kill my forum/database !!! lol




Q1- I think the above would be more efficient than simply adding all the new fields to the thread table! Although again, please confirm this for me. Would be appreciated!


Q2- Where do I put the relational fields (6.A or 6.B ... and ... 7.A or 7.B or 7.C)? In the new REVIEW table (with a column indicating the thread_id of the review?), or add a 'review_id' in the POST table that would lead to the unique key field of the new REVIEW table? Not sure which way is best or most efficent.


Q3- Do you all consider vB3 established enough to do this sort of hack?


Q4- Is there anything else you recomend I may have missed or overlooked?



I think that explains it all ... my site is www.homeofmusic.com , but the forum is hosted at http://www.homeofchat.com/vB/

Thanks to all for your help/feedback ... even if it's just to say I'm on the right track !!!