Handling large mysql database in php

Hi all,
 We are developing a job portal in php and using mysql as database. Total number of fields to be stored in mysql are about 90. So  here the challenges we are facing-
1. Shall we store all the fields in one table or distribute in separate tables.
2. How to store multiple entries in one field set like someone may have multiple certifications/qualification/degrees, since the qualification option is kept dynamic and user may enter as many as certifications etc.

Ramesh JangraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
1. One table unless there is a reason for breaking them up. It makes your queries a lot simpler as you do not have to join tables to get all your data

2. I use JSON to store complex data in a field. This is works very well as long as you don't need to order or join the field based on values in the field

However, you should also take a look at relational database design which calls for child records to be stored in separate table and linked to a parent record through a primary key on the parent.

So in your case you would have the parent table which would then be linked to 0 or more records in the qualifications table - the data would be retrieved from the database using a JOIN.

Whether you use child tables or JSON encoded data depends entirely on how you plan to use the data.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
Just a guess, but it seems like you have a many-to-many relationship between people and certifications.  This would seem to imply separate tables and a relationship between the tables.
Mark BradyPrincipal Data EngineerCommented:
I prefer to separate tables suggestions above. For example take a user table. This can store most important things that every user will have. Example: username, password, email_address, phone etc...
Then you would have things that not every user will have an entry for in another table called user_attributes.
The attributes would be things like favorite_color, vehicle_make, vehicle_model etc and these fields would be optional. Some users will fill these out and some won't.

When you want to pull a complete record form a user including all attributes you can do this without a table join query as long as the attributes table has the primary key as the userid. The attributes table only need to have 3 columns. userid, key, value

Quick example using the above information.

SELECT  username, password, email_address, phone, key, value FROM user, user_attribute WHERE user.id = 'xyz' AND user_attribute.userid = user.id LIMIT 1;

That will give you 1 row with all of those fields for that user.
Ray PaseurCommented:
Wow, what did we do wrong here?  Why did you mark the grade down?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.