具有自定义列标题的水平与垂直SQL

Question

I'm struggling finding the most efficient way to approach this DB design. This is for a Client who want to manage their Contacts. The Software currently is Horizontal which allows each Client to modify up to 5 columns for Field label questions. These 5 static columns are also in the Contact's table to store the results per Contact. For this example we are only using two tables, Client and Contact.

The Issue. I have been asked to extend this so a Clients can have an N amount of custom Field Label questions. One Client my want 1, another may want 20, 34, or N. I can't see this being done correctly Horizontally but easily vertically. The issue then becomes, I have to make a pivot to display the Vertical results in a row/column display.

The Question. Am I looking at this wrong, and should I change my perspective, and stick with a Horizontal table? If so, could someone explain to me the approach I should take?

Answer 1

I would consider storing the data in XML in a 3rd table with a FK relationship to the Client table. Your xml would have additional elements for each N number of custom fields. Then change the application so instead of displaying static fields, it derives those fields from the XML.

The plausibility of my answer depends somewhat on the architecture of the application displaying this data and assumes you have control over changing it. Since the question is a bit vague, and I don't even know what language your app is written in or whether it is web based or not, I can't really provide you with any code examples.