Multiple records with less information or one record with a lot of information in MySQL

Question

This question is performance related. I'm working on a project where I have a lot of tables with very few columns but multiple rows for one object.

For example a table "person_data" where I only have the columns "personID", "field" and "value". For one person I have 20 records like someID, "name", "someName" ; someID, "surname", "someSurName", etc ...

When reading or writing data we always write or read ALL values for 1 person. From a performance point of view would it be better to have just 20 columns in the table "name", "surname", etc ...

Thanks for your advice.

http://stackoverflow.com/questions/1125004/which-is-more-efficient-multiple-mysql-tables-or-one-large-table Similar question. Read the answers and you should get the idea — Darvex, Dec 27 '11 at 09:18
You are using EAV: http://en.wikipedia.org/wiki/Entity-attribute-value_model — ypercubeᵀᴹ, Dec 27 '11 at 09:24
Check this question: http://stackoverflow.com/questions/4066463/should-i-use-eav-model — ypercubeᵀᴹ, Dec 27 '11 at 09:29
@Darvex: He doesn't have many tables but one table (and many rows for one record) — ypercubeᵀᴹ, Dec 27 '11 at 09:36
@ypercube Thanks for your input, now I know my situation does have a name (EAV) I find a lot of info on StackOverflow and Google. — Dieter Depuydt, Dec 27 '11 at 09:50

score 0 · Answer 1 · answered Dec 27 '11 at 09:26

0

If attributes number for some object isn't chenging frequently you should store that attributes in same table. Selecting data from one table is faster than from many tables at same time using joins and foreign keys.

answered Dec 27 '11 at 09:26

piotrekkr

2,785
2
21
35

Multiple records with less information or one record with a lot of information in MySQL

1 Answers1