Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

First let me describe my case:

I need to store around 200extra detail of each user. I am using wordpress. Now there is a default function to save data associated with user. But i am not sure what should i use. In my case i will need some of data in 1 page. For example i need 20 data in one page and 100 data in another. User may need 20 data at once or 100 data at once.

I didn't designed my database table yet. After understand the best way of query i will design it. To get 200 data (fields) of a user if it is ok to make 200 search query then i will user wordpress default meta table. Structure is given below.


| umeta_id | user_id | meta_key | meta_value |

From structure above you can see wordpress save each data of a single user as a single row. SO in this case for one user's 200 data wordpress will save 200 row. And to retrieve the data i have to make 200 queries.

Now if it is better to get all data of user by a single query and save it in session then i will create my own table. Structure is given below.


| User_ID | User_detail_1 | User_detail_2 | User_detail_3 | ...

In the above structure i can save all those 200 data in one row and can get all in one query and then save to session and user when needed somewhere.

Now in my site i dont need all data in one page. On one page i might need 100 data and in another page i might need 50 data. So considering my case should i save each data as a row(using wordpress meta table) and then make 100 queries for the page where i need 100 data and make 50 queries where i need 50 data?

Or i should use one query and get all the data, save in SESSION then use data from SESSION where 50 or 100 data is needed?

share|improve this question
    
you need to store 200 what? Fields? –  Chris Travers Mar 25 '13 at 6:48
    
Yes. I need to save 200 fields. –  Adil Mahmud Choudhury Mar 25 '13 at 7:43
    
Could you give more information on how the user's data is stored? Is your problem performance related (as the title could let us guess) or more on how to get the proper results? –  KookieMonster Mar 25 '13 at 7:46
    
I have added more detail to the question, please review and let me know if you have anymore question? –  Adil Mahmud Choudhury Mar 25 '13 at 8:04
add comment

1 Answer

Thanks for the clarification. The first proposal is the infamous EAV, Entity Attribute Value. It is my daily pain as it is the model used by a 3rd party tool. If you want to have a fine list of why it is usually such a problematic model, please have a look at Bill Karvin's "SQL Antipatterns". You will have problems with data types, multiple values, enforcing integrity...

Therefore, I'm all in favor of the second model. If the volume of your data is really important, you can consider partionning, especially vertical partionning.

share|improve this answer
    
+1: Karvin's book should be required reading or anyone doing DB work (even us people who consider themselves to be pretty knowledgeable already!). I particularly like the fact he covers "circumstances when you might have/want to use pattern X anyway" and "mitigating factors/methods". The EAV pattern (you will often also hear it called a "property bag" pattern) can be very useful in some circumstances but careful thought is needed to make sure your end use isn't one that'll make it an absolute pain later. –  David Spillett Mar 25 '13 at 13:20
    
Thank you for your valuable reply. I am actually looking for something else. My actual question was should i do 50 to get 50 data or should i do 1 query and get all 200 data and save in SESSION then use 50 data from there? –  Adil Mahmud Choudhury Mar 25 '13 at 14:24
    
Sorry, I could not understand... How do you determine how many fields to display? Do those fields always stay the same? Do you have an idea how often you will have to display 200 and how often only 50 ? –  KookieMonster Mar 25 '13 at 14:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.