MyBB Community Forums

Oops, i must have mis-read it My bad :p

O my goodness, think of how complicateds the 1.6-2.0 upgrade script would be O.o

(2011-12-09, 11:27 PM)Paul H. Wrote: [ -> ]O my goodness, think of how complicateds the 1.6-2.0 upgrade script would be O.o

It's not that hard you know? I think the database scheme will change anyway (am I wrong?)!
It's easy to do something like:

echo "Upgrading mybb_table \n";
$num_rows = mysql_num_rows(mysql_query("SELECT * in mybb_table"));
for($i = 0; $i < $num_rows; $i++)
{
   //get row $i of data into variable $var
   mysql_query("INSERT INTO mybb_new_table ('column1', 'column2', 'column3') 
VALUES (".$var['column1'].", ".$var['column2'].", ".$var['column3']".)");
}
echo "mybb_table successfully upgraded \n";
echo "Upgrading mybb_table2";

(2011-12-09, 10:34 PM)TheGarfield Wrote: [ -> ]That's because you don't think of a normalized database, in a normalized database, only a key should be used...
Example, in your above example, you wouldn't manage to find the posts of "bob" but of bob's ID (example 132), and that's as easy as your previous query...

Bob's username is stored one time, and you can easily get it by requesting the 'users'...
Because typically anything that is not a "key" isn't unique... If the username is unique (is a key), then you won't need the user ID...

Re-think of it

But I don't know you as user 44708, I know you as TheGarfield, so I would search for your posts using your username. So I could search the posts table directly and get your posts, or I can search users to get 44708 and then search posts for uid 44708 or the preferred method in this case of using a join, but still searching from two tables and taking 6x as long.

However, this is all a moot discussion because all the queries I see MyBB using are based on unique keys (UID, PID, TID, FID, PMID, GID, SID, etc) and are join type queries to get, for example, user and post info all at once. I am not sure if the posts username field is a caching mechanism or a used in some other limited fashion.

i am not arguing against normalized databases, I am simply arguing that they are not always the most optimal method, especially in the cases where reads happen much more often than writes.

^
In a read-write database Formal Norms are always the optimal method, now even if your forum is just read (no users actually writes anything), you still write in database, (update thread views, update time, last login, currently online users), I mean there's always something done related to the database...

And just to answer your question, you can't do a query based on my username, it's NOT a key, since you're the programmer, you shouldn't manage my username, but my user ID.. You can't do a program that relies on my username, imagine that between the time you've submitted the request and the time it has been processed by the database, the admin has changed my username to TheGarfield2 and another user has registered using TheGarfield, you will get the wrong person (that can't happen but in reality that's a very big issue) ...
Managing unique IDs only to get data from a database = That's how solid programs work


A read-only database (if that term exists), is a database that is stored somewhere, and that clients can only do SELECT queries...

please don't misunderstand me, I use keys/indexes in my stuff and know that searching on keys is most efficient.

back to the searching by username example, the MyBB search page has a username field, not a UID field. So there will always be searches on a username that can be changed at any time.

That said, MyBB is still using a join to get posts where the uid matches the uid returned from users with that username. And that username can still be changed between requests.

My arguments are only concerning the request for data that is already in the table being queried and are not about trying to join multiple tables on username. If I only need the fields in the posts table from a specific username, it is more efficient to query on username (which should be indexed in that case) than it is to run a join. That is all I am saying.

Now, if I need all the post info and the user's location, b-day, number of posts, etc, then obviously a join would be the best solution using a where clause on the user table (though if indexed the posts username would technically work as well).

Keeping username in the posts table as well as the user table is just not as efficient storage wise or with regard to a username update. This I am not arguing.

I got it, I see what you mean, and that's true
You can't force users to do everything using user ID, like logging in will always be with username, so if the admin changes the username, a user can't login, even if his password and UID remain unchanged

Actually, storing usernames in addition to user id makes sense in MyBB because of guest posting. If a guest posts something, the UID is 0 but the guest could provide a name to distinguish from other guests.

Fair suggestion OP.

(2011-12-18, 04:44 AM)laie_techie Wrote: [ -> ]Actually, storing usernames in addition to user id makes sense in MyBB because of guest posting. If a guest posts something, the UID is 0 but the guest could provide a name to distinguish from other guests.

Ok so in that case, the administrator allows guests to post and choose their own "username" but still they aren't registered, so why not add that guest to the "mybb_users" table with all the information (username, ipAddress etc) and GID (group ID) = "0" (or whatever the Guests group is???) ??

This way, automatically all the chosen parameters for "Guests" group will apply to him.
So whenever a user is online, PHP will check if he's already opened a session (if so, the user is registered and authenticated), otherwise he is a guest, when he tries to post, (if he's allowed to do so by the administrator), if he's already chosen a username as a guest, he will continue using the same username, otherwise he has to enter a new username, it's really simpler.

I'm just talking about organization and fast db response, you really can't imagine how fast a BCNF or 3NF database is, compared to a 1NF or no Normal Form database...

(2011-12-18, 04:54 AM)Mebes Net Wrote: [ -> ]Fair suggestion OP.

Thank you