XF 2.1 How to: mainTable.new_field or newTable.newfield and left join

Robert9

Well-known member
Let-s say we have an add-on with these functions:

Option to save a rating inside/for a post, if the post has more than x words.
Means we need a field rating and the word count add-on from xon.

Now my question: should i save my post rating in
a) xf_post.rating (i have maybe 5% ratings, means 95% posts doent need this field)
b) a new table (less rows, but another left join for showing posts)
c) anyway i need the add-on from XON to count words, this table is also left joined for the posts, so i could add just one more field here.

Next question:
To save the calculated value {sum of ratings}/count of ratings i will do this in thread; here we also have the question where to save
a) in thread (i prefer this, because i want to have the value also in the threads list)
b) in a new table, because we have this value maybe in 5% of all threads

And finally i need a strategy to calc and save the value for the thread, examples are:

a) save xf_thread.rating as an array with {sum} and {cnt} of posts; means i cant sort it in real time; this means i can add and edit the rating of a post
new post => cnt++; sum+=new value
edit post => sum - old value + new value (means to send the old values in form also or use again a query to fetch post data)

b) use two fields, i can sort threads by them and can do the same like above; maybe i could use a third field for sum/cnt also to search?
(or better do a cron and fetch them all in the night and save the results for top x in forum y in a new table?)

c) i can run a cron every night, but for what?
All threads updated in the last 24 hours => check for post with rating => sum and calc values


I have not enough information to decide this:
1. Extra table means less memory, but needs a left join. Added field to XONs wordcount-post-table means less fields for post, left join is anyway done, but combination with another add-on (that is anyway needed).

2. save rating in thread, means 95% of the threads dont need the field, but we need no new left join;
XON also uses this way for his word-count add-on, any maybe he knows how to do such things.

3. Real time calculating needs again queries, cron job is done at night, but the results are not real time calculated meanwhile.
But to stay in xf_thread solves some problems with deleting threads or merging.


Maybe someone has an idea how to handle these typical situations? Every time when i plan a new addon i have the same questions like this.
Add fields to the main tables (when sorting), add arrays to main tables (when not sorting), add new table, when the left join seems to be no problem, especially when it is no main function like threads or posts and/or when they are really less rows compared to the main table.
 
Back
Top Bottom