This topic recently came up on the forums.mysql.com site.
The opinion expressed was that pivot tables are very hard to scale and maintain it would be worth a redesign of schema instead of a pivot table. This is a valid opinion with valid points.
I would like to add the topic here to help express my point of view and have it available for others.
It all depends on the data being gathered on if you should use a pivot table or not. The example given in a previous post by me was just an example of how they work.
If you are collecting known user information (First and Last name, Address information, Phone ) then yes a pivot table is more complicated that what you would need. If you just have a few data points to tie them to outside of that core information then yes another table is a solution and tied with a simple join.
The pivot table concept is valid when it is for dynamic amounts of data per entity you are collecting.
You might need 10 data points for 100 users. You might need 500 data points on the next 100 users. Can the schema handle it easily?
The example given in previous post I agree does not require a pivot table. But I just used the concept given to me in the forum to answer the question asked.
Ideally you can use both solutions in your schema. Core data points, keep in columns. Dynamic data keep in pivot tables.
If it is built correctly it is very scalable, the billions and billions of data I stored in the pivot table proved this to me easily. That does not mean it would not require some work. You very well might find that creating some views or summary tables that look into the pivot table would be easier for others to gather data. This begs the question then why wasn't the data stored that way in the first place? Again it depends on the dynamic nature of your data and application that uses the data.
