MySQL multiple tables or serialized data

Question

I'm writing up a job recruitment site for ex-military personnel in PHP/MySQL and I'm at a stage where I'm not sure which direction to take

I have a "candidate" table with all the usual firstname, surname etc and a unique candidate_id, and an address table linked by the unique candidate_id

The client has asked for more data to be captured, as in driving licence type, religion, SIA Level (Security Industry Authority), languages spoken etc

My question is, with all this different data, is it worth setting up dedicated tables for each? So for example having a driving licence table, with all the different types of driving licence, each with a unique id, then linked to the candidate table with a driving_licence_id cell?

Or should I just serialize all the extra data as text and put it in one cell in the candidate table?

If you are willing to have a major headache of modifying that data - go on and serialize it (my personal experience). At the moment I can imagine just one valid use case for this - those data will be used mostly by javascript, so you just serialize into JSON and be happy. — J0HN
– J0HN, Commented Jan 17, 2012 at 13:07

Your Common Sense · Accepted Answer · 2012-01-17 13:05:37Z

5

My question is, with all this different data, is it worth setting up dedicated tables for each?

Yes. That is what databases are for.

answered Jan 17, 2012 at 13:05

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andy Over a year ago

Would there not be a decrease in performance due to all the joins required?

Your Common Sense Over a year ago

performance has nothing to do with this question at all. It is absolutely irrelevant matter.

mtsr · Accepted Answer · 2012-01-17 15:51:46Z

4

Dedicated tables versus serialized data is called Database Normalization and Denormalization, respectively. In some cases both options are acceptable, but you should really make an educated choice, by reading up on the subject (for example here on about.com).

Personally I usually prefer working with normalized databases, as they are much easier to query for complex aggregated data. Besides, I feel they are easier to maintain as well, since you usually don't have to refactor when adding new fields and tables.

Finally, unless you have a lot of tables, you are unlikely to run into performance problems due to the number of one-to-one joins (the kind of data that's easy to denormalize).

edited Jan 17, 2012 at 15:51

answered Jan 17, 2012 at 15:40

mtsr

3,1521 gold badge16 silver badges21 bronze badges

4 Comments

Andy Over a year ago

The extra data is not going to be used for querying, so I think serializing is the way forward. Thanks!

Your Common Sense Over a year ago

bulk storage has nothing in common with Denormalization

Your Common Sense Over a year ago

@Andy this assumption (not going to be used for querying) is going to be grave mistake. The data demands to be queried. This is job recruitment site, dude. An employer may (and will) wish to see candidates with certain abilities, like certain SIA level etc.

mtsr Over a year ago

Like I said in my answer, I prefer normalized databases, they're simply more future-proof and apart from very large scale examples the performance usually won't be a problem.

Rich O'Kelly · Accepted Answer · 2012-01-17 13:04:59Z

4

It depends on whether you wish to query this data. If so keep the data normalised (eg in it's own logically separated table), otherwise, if it's just meta data to be pulled along for the ride, whatever is simplest seems reasonable.

Neither approach necessarily precludes the other in the future, simple migration scripts can be created to move the data from one format to the other. I would suggest doing what is simplest to enable you to work on other features of the site soonest.

answered Jan 17, 2012 at 13:04

Rich O'Kelly

41.8k9 gold badges87 silver badges114 bronze badges

Comments

Neto Yo · Accepted Answer · 2014-02-04 22:33:35Z

2

You must Always go for normalization, believe me. I made the mistake of going through the easy way and store data improperly (not only serialized, implode strings of multidimensional arrays ), then when the time came i had to re design the whole thing and it was a lot of time wasted.

I will never go by the wrong way again, clients can say "no" today, but "report (queries)" tomorrow.

answered Feb 4, 2014 at 22:33

Neto Yo

4492 gold badges5 silver badges17 bronze badges

Collectives™ on Stack Overflow

MySQL multiple tables or serialized data

4 Answers 4

2 Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related