How many fields are OK to have in a MySQL table?
I have a form to get information from users, it is divided into 6 or 7 unites, but all related to each other. They are about 100 fields.
Do you recommend to keep all of them in one table?
A:
MySQL supports up to 4096 columns per table. Dont use a field in main table if it often takes empty values.
if a column takes NULL no problem but if one stored as " " its memory wastage.
Too often null values means optional field data then why should one store it in main table.
Thats what i meant in the statement.
Paniyar
2010-10-12 11:02:25
Can you substantiate your statement?
The Elite Gentleman
2010-10-12 11:07:37
A:
If they all relate and can not be normalised (not to mention nothing > 1 is serialised) then I guess it will be OK.
Are you sure some things can't be split into multiple tables though? Can you provide a sample of the information you are gathering?
Could you split things into such as
- user_details
- user_car_details
- ...
alex
2010-10-12 11:02:40
+2
A:
Providing you are following database normalization, you generally should be ok - although you may find some performance issues down the road.
To me, it seems like perhaps there could be some normalization?
Also, you should consider how many of these columns will just have null values, and what naming conventions you are using (not just name, name2 etc)
In case you want to read into it more.:
Tim
2010-10-12 11:02:57
+1
A:
There's no general rule to that. However, 100 columns definitely hints that your DB design is plain wrong. You should read about normalization and database design before continuing. I have a DB design with ~150 columns split up into nearly 40 tables, just to give you an idea.
Kawu
2010-10-12 11:03:47
The attributes you are modeling might all relate to a single entity, but that's just a typical DB design situation. Consider a car and an engine. Even though there's just one engine doesn't mean it shouldn't be put into its own table (one-to-one relationships).
Kawu
2010-10-12 12:03:47
I've put up a somewhat DBMS-independent, more practical guide on SQL and database design which also handles MySQL oddities here: http://www.kawoolutions.com/SQL_Database_Design
Kawu
2010-10-12 12:05:28
A:
I would typically start splitting them into separate tables after around 20-30 columns. However, it is really driven by how you are going to use the data.
If you always need all the columns, then splitting them out into separate tables will add unnecessary complication and overhead. However, if there are groups of columns that tend to be accessed together, then splitting along those lines with a 1-1 relationship between the tables might be useful.
This technique can be useful if performance is a concern, especially if you have large text columns in your table.
Lastly, I would echo the other posters comments about normalisation. It is relatively rare to have 100s of columns in one table and it may be that you need to normalise the table.
For example columns, SETTING_1, SETTING_2, SETTING_3, SETTING_4 might be better in a separate SETTINGS table, which has a 1-many relationship to the original table.
ar
2010-10-12 11:08:22
A:
Thank you all for your responses,well my table's data are about some personal information of users,all the work that is needed to be done is to get them from the user,save and show them and have the edit option. should i use the normalization? doesn't it increase the process time in my situation?
A:
For data that does not need to be indexed or searched I create a single column in my database like col_meta which holds a serialized PHP array of values, it saves space and can expand or contract as needed. Just a thought.
woodscreative
2010-10-16 20:44:35