DB Design Need Suggestions

Question

Approach 1 and 2 Example.docx ---DB Design Need Suggestions--- I have Order number XYZ123. for this order I have 15 editable grids( A,B,C ..etc ) were user enters data. The grids are structured as column1 contains lables and remaining columns user enters values. The number of lables may vary for every grid like. A has 2 columns 10 rows. B has 3 columns 6 rows. C has 4 columns 12 rows ...etc Approach 1: maintain a separate table for each grid (15 Tables) and write the values by maintaining the primary key of that Order number as relation. 
 or 
 Approach 2:Maintain a single table which is combination of all editable grids in a flat file format (Storing the data vertically) so that the number of columns will be constant in the table. write the values by maintaining the primary key of Order number as relation. 
 Things to consider: ->We have to build records, where we to show the submitted data in paging Grids. ->After the submition of data user should be able to edit the data through related action(If we store data in single table way like flat file (Storing the data vertically) , to get the data and display in the UI again we have to Map the data to existing structure of XSD of the grid. 
 Please suggest the best approach. 
 Thanks in advance

kondetiv · Accepted Answer

Always your DB structure needs to be normalized as much as possible. I would suggest going with approach 1 is better. It has an advantage of editing data for a particular grid through the related action for example.

vijayana · Answer

Agree with kondetiv and as per Appian best practice ( community.appian.com/.../project-implementation-best-practices-checklist) 1) Keep a limit of maximum 50 elements per CDT and 2) Keep a limit of maximum 20 CDTs per data store. 
In this case if you go with the 2nd approach, you will end with more than 50 elements and it may create performance issues in many ways. The better approach I believe, the first 1st approach. In this way you can create a separate datastore and respective cdt's which make a good modular approach design patten.

Reggie · Answer

For retrieving information, for record display purposes, take a look into creating a view (and also separate/read-only interfaces) to mitigate query calls against tables that would be created in your 1st approach. 
 
I'm not aware of your user base size or how often the records will be used during typical high traffic times, but given the # of tables and fields from your situation, combined with the 1st approach, this would probably also require you to choose which fields are necessary for the view to draw from instead of selecting every field from all 15 tables in your view's definition. 
 
I hope this all makes sense and is helpful to you.