I am trying to make a simple application. This application will consist of projects, for which the data entry operator will fill in the values.
So, it will be a typical form. the x axis (so to say) of the page will consist of activities (which may also (or may not) have subactivities) and the y axis will consist of the numbers against those activities.
So for this the following are the tables that I have thought about and I thought I could request for some suggestions to improve it. I may be completely wrong in a few places, wont know till I begin.
Login (typical login table)
Project_Details ( this will contain the project name, project start date, end date )
Project_Attributes ( this will tell if the project has one or many subactivities )
Project_sub_attribs (this will contain the activities listed in the attribs. ) So for ex, if a proj has A, B, C attributes, this will contain (A - a,b,c,d, B- a,b,c,d).
Project_metrics ( this will contain the headers/headings/column names that go with the y axis. So metrics of a project go here)
Project_data (I am thinking this will have the actual data being fed in by the data entry guys. So, this will have a proj id (from proj details), proj attribs and sub attribs from the above two tables).
I am guessing, I have gone terribly wrong in designing this. Because, I think the project_data might have a little too many records. I will also have to be able to do some report generation stuff on this.
What do you reckon ? Can I request for your suggestions please.
this is how my final jsp table shoul look like :
metric1 metric2 metric3
heres what I was able to come up with and the tables I have are :
Projdetail (contains PK pid)
projattribs(pid FK references pid from projdetails and contains a list of projattribs for a proj)
projsubattribs(subattribId FK references projattribid from projattribs, pid FK references pid from Projdetail)
projmetrics (pid references pid from proj detail and a list of metrics for a proj)
This form will take inputs and store into a table called projmetricsdata(contains pid, attribid, subattribid, metricid, metricdata, dataid).
So, my question is :
1) is the above design (ignoring the naming conventions and typos), good enough ?
2) I am trying to form a query with the plain old sql and trying to come up with queries for
1) displaying the left col with proj attrib details
2) displaying metrics row on.
So, for 2.2) all I need is a java bean populated with the metric table values.
but for 2.1, I dont know how I will populate this(probably using a join). I think I will need to use a join for this, but then, how would I populate the sub attribs for this (in java) ?
And another question I had was, say everything eventually happens and I need to take the inputs from the HTML table in JSP, how would I map the required text box to the required tables ? Ok, as I write this, I think think I can make use of the pid and attrib/sub attib id and make a way with this.
Joined: Jul 08, 2007
ok the metrics is left auto aligned automatically. there should a few tabs before it. So its like an excel sheet like a row header, you know what I mean.