How many fields is too many for a mySQL table?

Not sure how modular to make my site functions

[roldar]

I've got a site where users have three options:

1) Create a basic profile
2) Create an advanced profile
3) Create a custom profile

At first I considered breaking these into three separate tables. The problem is that when I want to show the "10 newest profiles" or something of that sort, I'm going to have to search all three tables and do some other computations in order to bring up just the most recent 10.

Furthermore, I would have three profiles all with the same primary key # (0, 0, 0, 1, 1, 1, etc.). This part seems like it's just asking for trouble. When I have profile.php?id=3, there will be no way to tell which profile type is being called. So I'd have to add another variable, which, which I mod_rewrite to static-looking folders, is only going to appear to separate the profiles by type.

The different profiles aren't really any different -- it's just user preference. I'll have some pages that mix all three profile types without discrimination, ordered by some other common attribute (male/female, location, etc).

I really wanted to make this as modular as possible, but the more I think about it the more I worry I'll just create a mess.

Right now my thinking is to have a single table with all the profile types mixed in it, and then just have a char field in which I note the type of profile. This would mean the basic profile would have about 75% NULL fields. The advanced profile would have about 50% NULL fields, and the custom profile would have about 90% NULL fields.

I'm not sure if all these NULL fields are going to create a lot of overhead or not. If they were in separate tables I would eliminate a large number of them, but I'd run into some of the problems I worried about before. I don't forsee having more than a few thousand records at any one time, but this could get up into the 10,000's.

Also, how many fields should I limit myself to? Is it in the 10's, 100's? I think I'll have about 75-100 fields for each record. These are all going to be text-only, so each individual record isn't likely to take up more than, at most, 20-50kb.

Another thing I'm pondering -- what's the best way to handle user-uploaded pictures, or anything where there is not a set number? I considered adding another 20 fields to each record for picture_1, picture_2, etc.; but I really don't want to create a physical limit in the database. So then I decided I'd add another table for images, which would include the image URL and a reference to the profile. Then when the profile is brought up it SELECTs all the images for that profile. Is this the optimal way to do something like that?

Thanks in advance for any comments or suggestions.

(p.s. This isn't a dating site)

[vacorama]

tables can have a lot of things in them, but to keep things from getting messy, it's best to have a table for each thing that needs to interact with another thing. like with your pictures table. there should be tables for everything. for instance, if you were to have members that were gouped together somehow, you should have a table for members and a table for groups as opposed to having just a group id column or something. then you only need to access a few tables for your 'selects', and do most stuff with 'where' clauses. situations where you can have member id's colliding are bad. i would go through all your criteria, and how it's going to be used externally by the visitors, and see what advantages you'd have by being able to have tables for each. as long as there is primary key for each one they'll stay organized if you don't repeat fields for no reason.