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?
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.
Don't worry about the orientation of the columns; have PHP handle the presentation.
Store each of the custom column names in a table Columns with columns ColumnID, ClientID, ColumnName, and SortOrder. Create a second table ColumnValues with columns ColumnID, ClientID, ContactID, and Value, and store the values that correspond to each of the custom column names for every one of each client's contacts.
Have your application code build an HTML/CSS table with the following logic:
ColumnNames for the ClientID you're working with, in the same order as the SortOrder values for each of your ColumNamesContacts for this ClientID. On each row, list the ColumnValues for each of the for the Contact that corresponds to the row you're building, in the same SortOrder, for this ClientID.