MyBB Community Forums

So can you give us some examples for where the MyBB schema is not up to par according to you? Other than the username in posts tables, which we have found that is used for guest posting.

(2011-12-19, 04:40 PM)pavemen Wrote: [ -> ]So can you give us some examples for where the MyBB schema is not up to par according to you? Other than the username in posts tables, which we have found that is used for guest posting.

I'd like to know this as well. Where exactly are these inefficiencies?

And I did as much reading up on the subject as I could before posting.

(2011-12-19, 07:10 PM)labrocca Wrote: [ -> ]

(2011-12-19, 04:40 PM)pavemen Wrote: [ -> ]So can you give us some examples for where the MyBB schema is not up to par according to you? Other than the username in posts tables, which we have found that is used for guest posting.

I'd like to know this as well. Where exactly are these inefficiencies?

And I did as much reading up on the subject as I could before posting.

I admit that there are a few places, like posts table having both TID and FID since you can get the FID from the TID in the threads table.

but fixing that goes back to my argument about reading vs writing and what is more efficient in the long haul.

Examples:
- parentlist attribute in forum table makes it non-1NF = denormalized database
Utility: when an attribute is a list, it's hard to make simple queries...

- rulestype, rulestitle, rules, there's a functional dependency between these 3 elements, they shouldn't be in forum
Utility: rulestype is an independant attribute, separating them will make less redundant information...

- tid and fid in posts have a functional dependency, each thread belongs to a forum, from the tid you can get the fid
Utility: Let's say that you want to move any thread to another forum, you'll have to change the fid attribute for all its posts (suppose you have 500,000 posts)... if you delete that, one simple query to change the fid attribute in threads table is enough..

- Endlessly repetitive ip or ipaddress attributes, if you make them all in one table with a unique IPID, it will be easier to check for blocked IP addresses etc...

- privatemessages, you have 3 user IDs, fromid, toid, and uid, I don't see the utility of the third one, supposed that a PM is a message between 2 users....

- This is not something related to normal forums, but in the tasks table, there are many attributes than can be collapsed minute, hour, day month, weekday, can all be a DATETIME attribute that is more flexible and easily manageable

- uid, username repeated in threads and posts, lastposter, lastposteruid repeated in threads (suppose you want to change someone's username, run all the necessary queries)...

- additionalgroups in users is a list, same as the first, it's not 1NF and querying such tables is hard...

- regip, lastip, longregip, longlastip, again, badly managed IP addresses...

I've done a very very quick analysis of the database, there were many attributes that I haven't understood what they mean/refer to, I'm sure there are more than this list...

Anyway, I'm kinda sure this is not going to be implemented in MyBB 2.0, yet I think I've made a decent suggestion, supposed that those organizations who spent many time to find a standardized normalization for databases weren't stupid to make something bad...

Quote:- rulestype, rulestitle, rules, there's a functional dependency between these 3 elements, they shouldn't be in forum
Utility: rulestype is an independant attribute, separating them will make less redundant information...

Agreed.

Quote:- Endlessly repetitive ip or ipaddress attributes, if you make them all in one table with a unique IPID, it will be easier to check for blocked IP addresses etc...

Agreed. And I hate how longipaddress is part of the tables too. I don't see how that's a major benefit. Matter of fact ip searching with wildcards is very buggy since they have done so.

Quote:- privatemessages, you have 3 user IDs, fromid, toid, and uid, I don't see the utility of the third one, supposed that a PM is a message between 2 users....

I think the uid is whos the box it's in. Since a multiple user PM could be in 10 boxes. But yes at first site it appears as wasted space.

Quote:- This is not something related to normal forums, but in the tasks table, there are many attributes than can be collapsed minute, hour, day month, weekday, can all be a DATETIME attribute that is more flexible and easily manageable

Agreed but I think they did it for simpler php coding. Tasks is a really small table though so you're not saving any real overhead by changing this.

Quote:- uid, username repeated in threads and posts, lastposter, lastposteruid repeated in threads (suppose you want to change someone's username, run all the necessary queries)...

I disagree. I know that joining a marge user table with a large posts/threads table is intense.

Quote:- additionalgroups in users is a list, same as the first, it's not 1NF and querying such tables is hard...

Don't agree that it's hard. The php is fairly easy and imho the way it is has less database overhead than creating a new table then joining it constantly.

Quote:- regip, lastip, longregip, longlastip, again, badly managed IP addresses...

Yes I mentioned that.

Quote:Anyway, I'm kinda sure this is not going to be implemented in MyBB 2.0

Who can say? It's gonna be a huge rewrite. I'm really starting to loathe the uncertainly of the future of MyBB with that fact. MyBB is taking a huge risk imho with it. Using some framework no one is familiar with and changing all the standards. Every theme and plugin will break. Upgrades even if simple will only be so for those with no plugins and those willing to redo their theme.

And hey...thanks for arguing this out. Glad you didn't call it quits.

(2011-12-19, 08:08 PM)TheGarfield Wrote: [ -> ]Examples:
- parentlist attribute in forum table makes it non-1NF = denormalized database
Utility: when an attribute is a list, it's hard to make simple queries...

So you want to add another table that is a mapping from FID (category) to FID to handle the grouping of category, forum, sub-forum, sub-subfourm, etc? That is a lot of bookkeeping and consider the looping requirements for 3 levels of forums, let alone more than that.

(2011-12-19, 08:08 PM)TheGarfield Wrote: [ -> ]- rulestype, rulestitle, rules, there's a functional dependency between these 3 elements, they shouldn't be in forum
Utility: rulestype is an independant attribute, separating them will make less redundant information...

i think that it is more probable that rules are specific to a forum and have a low probability of being repeated between forums, so rules and ruletitle are fine in the forums table. ruletype is hard coded in the code to be a fixed selection. there is no need to remove ruletype

(2011-12-19, 08:08 PM)TheGarfield Wrote: [ -> ]- tid and fid in posts have a functional dependency, each thread belongs to a forum, from the tid you can get the fid
Utility: Let's say that you want to move any thread to another forum, you'll have to change the fid attribute for all its posts (suppose you have 500,000 posts)... if you delete that, one simple query to change the fid attribute in threads table is enough..

correct, you have to make one query to change the FID in your case, however consider how often a post is directly linked only by PID. Then we need to query TID and FID tables via a join to get the info we need. More temp tables. All the reading of the posts data happens much more often than moving a thread.

(2011-12-19, 08:08 PM)TheGarfield Wrote: [ -> ]- Endlessly repetitive ip or ipaddress attributes, if you make them all in one table with a unique IPID, it will be easier to check for blocked IP addresses etc...

But then you have to search if the IP exists and then do an update or insert accordingly. Yes, you can use a REPLACE INTO, but that is simply executing a DELETE and then an INSERT. Plus there can be issues with InnoDB foreign keys being orphaned, etc.

(2011-12-19, 08:08 PM)TheGarfield Wrote: [ -> ]- privatemessages, you have 3 user IDs, fromid, toid, and uid, I don't see the utility of the third one, supposed that a PM is a message between 2 users....

Got me there, but i think it has to do with the original author of the pm versus forwarded pms.

(2011-12-19, 08:08 PM)TheGarfield Wrote: [ -> ]- This is not something related to normal forums, but in the tasks table, there are many attributes than can be collapsed minute, hour, day month, weekday, can all be a DATETIME attribute that is more flexible and easily manageable

This is an issue related to how the data is displayed to the user via drop downs for each time component. its is very resource intensive to parse a timestamp for all its individual components in older versions of PHP. newer versions can parse into an associative array though

(2011-12-19, 08:08 PM)TheGarfield Wrote: [ -> ]- uid, username repeated in threads and posts, lastposter, lastposteruid repeated in threads (suppose you want to change someone's username, run all the necessary queries)...

- additionalgroups in users is a list, same as the first, it's not 1NF and querying such tables is hard...

but consider how it is implemented. additional groups is already a CSV and easily implemented in a query using a list e.g. IN (CSV list)

querying the data is easy since there is no need to query for those with a certain additional group. only primary the group is used for selecting users

(2011-12-19, 08:08 PM)TheGarfield Wrote: [ -> ]- regip, lastip, longregip, longlastip, again, badly managed IP addresses...

I've done a very very quick analysis of the database, there were many attributes that I haven't understood what they mean/refer to, I'm sure there are more than this list...

Anyway, I'm kinda sure this is not going to be implemented in MyBB 2.0, yet I think I've made a decent suggestion, supposed that those organizations who spent many time to find a standardized normalization for databases weren't stupid to make something bad...

Look, I am not saying a normalized DB is bad, nor am I saying that those developing these theories are not smart. However, being book smart and good in theory does not always translate well to the real world application of those theories. Trust me, I am an engineer and understand theory vs application

edit: added line feeds that somehow were lost the in copy/paste

Also, please don't forget that several things you may see as being redundant or otherwise better suited in a separate table are not things that can be. Things like rulestype. These are hardcoded in the code and are LANGUAGE dependent and thus are not suitable for the DB in the long run.

^
Those who've invented and/or those who've approved these theories are engineers too..

Just because it can be done does not make it practical.

(2011-12-19, 09:00 PM)pavemen Wrote: [ -> ]Just because it can be done does not make it practical.

You're cleverer than those who created databases and normalization structures, I got it, thank you!

not saying that... but not necessarily disagreeing

i have not once said that the 3nf and similar is not important and feasible. i am only looking at it from a practicality stand point such as implementation and priority of the usage. why waste limited resources making permanent tables for IDs and temporary tables due to extraneous joins when the benefits of the 3nf are specific to writing data and writing is only 5-10% of the read processes.