Adding addtional tables

[todd clare]

I'm cool with PHP and MySQL, but have more of a philosophy question.  Could post this to the general support area, but I figured people that lurk around here would be the ones answering it anyway.

I have additional information that I want to track about my users.  Just for argument, say "Favorite Muppet" and "Hair Color".

I see several options available to me to add this to my database:
- Add 2 fields to the member table
- Add a "dumb stuff I track" table to the SMF database, with my fields plus a memberID field keying to the member table, or
- Add a new database and put my data in there, and use external SQL calls to both to coordinate the data.

Technically, I'm capable of doing any of the above, but which SHOULD I do?

If I add to the table or add new tables to the SMF database, will those changes be lost when things upgrade in the future?

it seems to me that putting the whole thing is a separate database is the most reliable, stable, non[problematic way to go, but man, it make my php and SQL code gross having to connect to two databases at once.

Suggestions please, and if the answer is "separate database", can someone give me some sample code of querying the two tables (I usually live in one dB only) (so I guess I lied about being able to do any of my options.  )

Thanks!

-- Todd

[sm2k]

No reason to go with a seperate database.

Creating a new table is an ok idea if the number of pieces of information you want to store is variable.  If you have a fixed number of things, then it would be best to add fields to the member table to avoid having to do joins.

I shouldn't answer your question about what will happen during an upgrade of the member table (because I'm new to SMF), but assuming that the SMF team just uses an alter table instead of a drop and create, your fields should be safe.  You should always dump your data and schema before upgrading anyhow.