[General] Pivot or not Pivot
Posted by: Dave Young
Date: November 13, 2018 01:50AM

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

Options: ReplyQuote


Subject
Written By
Posted
[General] Pivot or not Pivot
November 13, 2018 01:50AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.