1

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?

Improve this question
4
  • you need to store 200 what? Fields?
    – Chris Travers
    Commented Mar 25, 2013 at 6:48
  • Yes. I need to save 200 fields.
    – Choudhury A. M.
    Commented Mar 25, 2013 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
    Commented Mar 25, 2013 at 7:46
  • I have added more detail to the question, please review and let me know if you have anymore question?
    – Choudhury A. M.
    Commented Mar 25, 2013 at 8:04

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • +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
    Commented Mar 25, 2013 at 13:20
  • 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
    Commented Mar 25, 2013 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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