Modify User table vs. using JOIN

[Guru]

I've noticed in several hacks that it is common to modify the user table to add fields. This table already has a fairly large number of fields, and adding several hacks that increase this number just makes those queries that much longer.

Another approach is to create a separate table, and use a JOIN SQL query to get the data, yet that seems to be seldom used. Is there a reason that hacks don't add a new table rather than modifying the user table?

---

As an example, consider the common hack of making a user contribution table. The hacks here add donate fields. (I'd like to see an amount, a date, and a field indicating whether the user wishes to remain anonymous.) This modification of the user table adds the donate overhead for every user.

The second approach would be to add a new donation table with a few fields. The userid would be the primary key. If only 5% of your users are contributors, your donation table would be much, much smaller. And, the hack wouldn't affect the user table at all. This should be a safer way to hack vB, shouldn't it?

Are there performance considerations, or is it just simpler to add a few fields to the user table?

[Admin]

I usually use the options field in the user table. I only use it privately tho, because if I start using it in hacks, I guess at least one more person will start using that technique (not that I mind, of course ). The problem would be, that two hacks and more will surely use the same flag (16, 32, 64, etc.) so that technique would become useless.

Using another table is a bit clumsy IMO, because then you won't be able to access the value without selecting it directly from the database (as opposed to using getuserinfo() and that's it), unless you modify the getuserinfo() function to do that JOIN.

[damule]

It is a way of doing it there may be a couple reasons it may not be commonly done

1 When an author crates a hack they try to keep installation as simple as possible, most of the people who make there’s hacks have a very limited understanding of VBB as a whole so it takes a lot of trial and error just to get their hack to work.

2 There is a big performance hit on the DB server.

When you do a SELECT on a table with 100 records, the server looks at 100 records. Now if you do a join with the same table with 100 records and another table with 10 records the DB server uses the data from record 1 on are small table and try’s to make a match with the records on the big table, so we’ve made the server look at 100 record so far and it does the same with the second record so we’re upto 200 records that we’ve made the server look at and now with the rest we come to a total of 1000 record views.

Hope that explains it, joins are never and easy subject.

[Guru]

Originally posted by FireFly
Using another table is a bit clumsy IMO, because then you won't be able to access the value without selecting it directly from the database (as opposed to using getuserinfo() and that's it), unless you modify the getuserinfo() function to do that JOIN.

That makes sense for the example I posted, especially if you want a special graphic next to the user name every time it is referenced.

[Admin]

Originally posted by Guru
That makes sense for the example I posted, especially if you want a special graphic next to the user name every time it is referenced.

Yup, that's my point.