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 !!!

[CarCdr]

You have the basics figured out imo. I did something quite similar for an issue tracking forum on our site.

Add columns to the thread table or add new table(s)?

It is easier to add columns to the thread table. The two reasons it is easier:

a) You do not had to add PHP code to the various scripts that delete threads to delete your table rows.

b) You do not need to add JOIN code to the various places that retrieve threads.

The cost is storage. You are adding N bytes to every thread table row. In a properly normalized database, one would never add columns to every row when it is data required only by a small percentage of rows, but it depends on the site (size of thread table). In your case, if you are adding only a few percent of storage, then I would go with adding columns. If you are adding 30% increase in storage, well, that gets a bit more worrisome.

What about searching/sorting?

I imagine you will want to beef up the search form, as well as the "Display Options" of forumdisplay -- the script that displays threadbits.

When is special entry being performed?

If I understand correctly, your scripts knows when it is entering a special thread based on a flag that is associated with a forum. No problem. You can modify newthread.php to run your code. But, you have to consider edits of the post as well (editpost.php) and maybe the thread edit code (postings.php).

Modularize your changes!

It is a far better hack if you put all of the code into a separate include(s) so that modifications to the base vBulletin code amounts to nothing more than a series of calls to functions. If the main include were "my_music_review.php", then everywhere you need the code you would have the following somewhere near the top of the vBulletin script you need to modify:

if (this is a music review forum) {
     require_once('./includes/my_music_review.php');
     do other setup stuff;
}

[DJ RRebel]

Thanks CarCdr ... you info helped me alot. I just really wished you had your forum listed so I could check it out ... lol.

Anyhow, yeah, the 'edit'/search complications I thought of while in bed last night ... lol.

That being said, I think based on what you said, I'd probably be better with seperate tables based on storage. I already have 32,000 threads, and I don't see these reviews ever reaching anything more than 5% to 10% of the total reviews. However, just adding the columns would be adding significant size to the threads table, as I'd need to add all the initial remix information datafields, as well multiple occurances of the download datafields to leave room for the posibility of 3 or 4 or more download links. I think the fact I almost definately need a seperate download data table to enable a many to one relationship between download data and reviews is the big difference between your and mine. I kinda overlooked this a bit when I first developed the idea. Also won't make me scared to add new datafields found to be important for reviews.

You modularize idea is going to save me tons of useless clutter !!! lol

Anyhow, do you have any advice on which way to relate the tables, (6 A or B, 7 A, B or C) ?


Would I be correct in assuming it would be best to put the relational linking data field in the table with the much smaller number of rows (a thread_id column in the new REVIEW table)? Or does the fact calling up each thread would force a search through all that smaller table to find the corresponding review row make it less efficient when instead having a review_id relational linking data field in the original THREAD table would target the proper review table row right away?


Same holds true for the dowload data subset table. Since it's many to one Is it more efficient to put the review_id (or thread_id, not sure which would be better) info field in the Download table, which would mean each review would have to search all downloads? or is it better to pay the price of size but have a download_id array that imidiately tells the review what the download rows are (if any)?


This sort of theory is the last lesson I really need before being comfortable with all of this ... I'm a little scared of hacking existing forum .php pages because I know I'll have to reinstall each hack after each complete upgrade, but so long as I stick to hacks that have a lot of benefit to my community, I think it's worth it. As you can see if you've visited my site's forum, I don't really have much of a problem with modifying templates anymore !!! lol

http://www.homeofchat.com/vB/


Anyhow, thanks tons CarCdr for confirming that I'm at least going in the right direction ... lol

And thanks to anyone else who has the time to respond to my additional table theory questions or even add more comments/opinions on my original post !!!

[CarCdr]

It's of no use to list our site as it is a corporate site behind https. User's are registered by admin's and security is taken seriously.

As regards the linking, just use threadid. Each table you add would be linked using the threadid. For queries where the data is retrieved for a thread, you would add a couple of JOIN's to get the data from your tables.

You might make it easier on yourself if you define the download table to be flat -- just create enough columns to handle the maximum number of download entries. This would give you column names like "download_link1", "download_link2", etc. One can get away with this when one is dealing with so little storage, as is the case with your idea. If you are not going to search the download table, you could even make it easier by storing serialized arrays as text fields rather than using multiple columns.

If you do not use this approach, you will have to use code similar to that for attachments, where you have a one to 0..N relationship based on postid. Your code would take the threadid of a hit, and then retrieve all the rows from the secondary table based on threadid.

[DJ RRebel]

Wow ... thanks tons ... your feedback is really a HUGE help !!!

I'm even happier that I understood virtually all of it ... lol ... except:

"Your code would take the threadid of a hit"


Thanks again !!!

[CarCdr]

Your code would take the threadid of a hit, and then retrieve all the rows from the secondary table based on threadid.

A hit is when you are displaying a thread from the "special" forum. For such a display, you will want to retrieve the other information not in the thread table. That is when you would do the lookup in your one to 0..N relationship table.

If you use a flat table for the download information, you would not need to do this as you could use JOIN's to get the information when the thread table retrieval is performed.

[DJ RRebel]

ok ... so it's just a term? I wasn't sure if you meant it was like a performance 'hit', where it would affect overall speeds?

The reason I think it might be better to have a seperate 'download' table, is because it would most likely be not just the link. I would probably want to include start-date and cut-off-date depending on the server, as well as a small interger field to use to associate with various icons for the different servers, and of course the link itself.

The reason I'd want this, particularly start-date, is that I may want to enter a batch at the begining of the week, but to spread server load and have them added over the course of the week without me having to 'toggle' any other switch as the week progressed or be forced to only add them when I wanted them to show.

Is there some particular performance or other reason using a 'hit' to a 0..n relationship table isn't advisable? Besides the obvious fact that it'll take me longer to learn to code ... lol ???

And thanks again CarCdr ... you've been a HUGE help in helping me figure out what to do !!!