Single or multple pages.

Anon

Hello,

For a web project I am currently working on I need to create a user table. Right now I plan to have about 30 fields in this table.. I don't know how many rows the table will end up with but I want to plan now for later..

Several of the fields will be included in WHERE statements and I am using MYSQL 3.23.36.

I am wondering if it is better to have one table with many fields (including blob/text) or 2-3 small tables that i could join together when getting data?

Explain: Having just a "users" table or "users", "profiles", "preferences" etc?

Also, are there any performance differences between blobs,texts fields?

Regards,
Kevin Young

Anon

Sorry I meant "Single of multiple tables"

🙂

[deleted]

Normally you'd only split up a table if you want to link one row to many others.
Each user can only have one profile and one set of preferences, so there's no need to split it up.

However you may still split the table up if that helps you understand your structure better.

dr3amscap3

There are other reasons to split out data other than to have a one to many relationship.

In your user table, if all 30 fields are going to be valid for every record then it probably makes sense to keep them all in one table.

If you are duplicating any of the data in your user table from other tables to make your query easier, you might want to reconsider that.

Blob and text you might want to pull out of your user table and put in another table (link them through some primary key, like userId -- int not null auto_increment...). I'd probably do that if size was an issue.

If you're going to have 30 fields in the table though be sure to index the fields likely to be used in a query, it should help with your select speed.

Anon

For the majority of my database maintence I use phpmyadmin.. It allows me to add index's to each column seperatley with the command example:

ALTER TABLE users ADD INDEX(user_id)

In my user tables I have several columns that might be used for joins.

My first idea was to use the above command for EACH column but I read an article on phpbuilder.com (http://www.phpbuilder.com/columns/tim20010110.php3?page=2)that says doing that could slow things down?

It said something about using a multi-column index insetad? Is this true?

Note: Most of the queries to my tables are like:

select * from users where user_id = "n"

select * from users where province="Quebec" order by joindate desc

and so on...

Nothing too complicated.