XF 2.2 'Dual' records (e.g. resource+thread) or thread only? How best to organise the database?

Stuart Wright

Well-known member
Prompted by the new thread types in XF2.2, I'm reflecting on the method of structuring data for addons going forward.

In lots of addons we currently have a table to store content (e.g. resources) plus an associated discussion thread.
Each entry in the database effectively has two rows (e.g. resource + thread) instead of one (thread only).
We use this method in the competitions addon, for example, where we have a row for each competition and a thread to go with it. I know there are many other examples of functionality which do this, and it seems to be a standard.

However, there are some obvious disadvantages in doing it this way.
  1. you (often) have to maintain the thread when you add/edit/remove the associated content. Maybe you have to change the content of the first post or add replies to the thread.
  2. you effectively have two pages on the site containing similar (or the same) content, which is not good from Google's perspective.
  3. you have tabs linking the content and the thread.
  4. you have an additional table in the database.
  5. you have to query more tables.

Can this not all be achieved (in some scenarios) using a new thread type and custom fields?
Do as much as possible using threads and avoid creating at least one additional table?
I'm sure other people must have considered this.

There are probably multiple tables used in the Resources addon (I don't know, I've never had cause to purchase it), and this is where my suggestion gets a bit more complicated.
With our competitions addon, for example, we have a couple of tables. One for competitions and one for competition entries. We'd still need to keep the table for competition entries. But with custom fields, there is nothing really stopping us from ditching the competitions table and doing everything using custom thread fields.
The competition itself would be a thread (like an article thread) and the competitions home page would be a list of competition type threads with custom field information displayed.

Granted there might be a lot of custom fields. But does this really matter? Maybe it would actually be slower to deal with lots of custom fields?

Putting this out there as a general suggestion for addon developers and the Xenforo team.
What do you think?
 

Mike

XenForo developer
Staff member
TBH, the answer likely does depend. There are going to be advantages and disadvantages across the board. Using a thread will certainly get you functionality and integration into "new posts", though it will potentially hamstring you in other places. There will be ideas that will be easier (or more straightforward) with one particular path and it probably won't be consistent.

Taking resources as an example, there are a number of tables here. While some represent similar functionality to threads (watching, prefixes), there are also a number of separate tables representing things like updates and versioning or download logs and reviews/ratings. You could build those off a thread, but there are also some situations where you'll likely be fighting what a thread wants to do, so you have to account for that.

A separate example with media relates to privacy controls, which are very different from the way thread permissions work and essentially make the concept incompatible. (Though as I implied earlier, with enough effort, you could probably workaround this, but there's a definite difference between building something to work the exactly the way you want it to and bending an existing system to do those things.)

If you view your competitions as a thread with some extra functionality, then it's probably reasonable to use the thread types system. There's still nothing to prevent you from storing all of your type-specific data separately. The perfect example there are polls. They're pretty much literally a thread with some extra data grafted onto it.
 
Top