[General] Pivot or not Pivot

[Dave Young]

Hi, I want to improve my existing database structure.

I am collecting different data from people.

Single Data: gender / age / ...
Multi Data: What kind of pets you own? (cat, dog and or fish)

The data (values) change over time and new values are added again and again. Therefore I decided to save the data as follows:

USER_ID | WHAT | VALUE
----------------------
1 | GENDER | m
1 | AGE | 28
1 | PET | dog
1 | PET | cat
1 | COUNTRY | de
2 | GENDER | w
.... and so on

So I'm as flexible as I can be to take in more data.

The data should be evaluated as follows later (with example output):

"How many women are in the database?"

GENDER | COUNT
m | 235
w | 123

"How many women live in Germany?"

GENDER | COUNT
w | 75


"How many pets lives in the USA?"

PET | COUNT
cat | 5
dog | 13
...

However, the data should also be evaluated crosswise:

"How many animals (cats, dogs,...) live with women/men in the different countries?"

PET | GENDER | COUNTRY | COUNT
dog | m | de | 15
dog | w | de | 0
dog | m | us | 3
cat | w | fr | 33
...


My approach was to create the data as a pivot view/table. But the table becomes very wide and the pivot view is only buildable via PHP of course the "unknown" "what" and "value" names.

Perhaps there is a better approach?

I'm still pretty much at the beginning and could change the complete data structure now!

I would be grateful for any tip.

David