Unanswered: Database design feedback and suggestions
I’m designing a database for a small project. I came up with two approaches but not sure which one is good or if there is a better way to do it. I would greatly appreciate any suggestions. Sorry for my long post but I just wanted to give a little background before I ask questions.
Little introduction about my project.
From the front end of our web application, user enters readings in the form of a table. This readings will be used for some calculations. Once calculated, results should be displayed in the form of tables to the user. There will be more than 15-20 result tables to be displayed to the user. So I created tables using JQGrid with editable rows and columns within a form to enter readings. And created dynamic tables to output the results as well.
We are using Knime analitical platform for our business logic which will need readings in the form of excel files. The output from Knime is also excel files. Now we need to display these output excel files in the form of tables on the front end and also store them into the database for future reference.
Every time a user logs into our system, and enters readings for calculations, user information, calculation settings,
and results (which is in the form of excel and pdf files) should be linked and stored in the database so that when the user wants to search for his old reports then all the related files should be displayed to him.
Which is the best way to store results and reports on the database??
What I came up with is:
1. I store the user information in one table "UserTable"
calculation settings in one table “CalSettingsTable"
And a bunch of tables to store the result files.
Save these result files in a file system and store the file paths in the database table.
Is this the right way to do it?
Please check the figures for tables.
So when “user2” searches for his “cal3” calculation reports then I can query something like
select * from UserTable, CalculationSettingsTable, Result1Table, Result2Table and Result3Table where (join tables with user and calculationName)
2. Or is it better to store the data from the result files in the database tables. If
yes, then how can I store multiple Result1 (or Result2 or Result3)files data in the same table for different users?
Suppose my Result1Table excel files are something like this:
In this case my Result1Table table would look like this
So I can have query like this if user2 is searching for “cal2” calculation reports
Select * from Result1Table where user=user2 and calculationName=cal2
Which is better? In the second method won’t the table grow bigger and bigger like crazy, since there will be rows added from many files?
Is there a better approach?
Any suggestions will be greatly appreciated.
Thanks in advance.
Last edited by Albetros; 01-25-16 at 18:27.
Reason: Tables were clear to understand. Uploaded pictures for better table view.