This week's book giveaway is in the OCAJP forum.
We're giving away four copies of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) and have Khalid A Mughal & Rolf W Rasmussen on-line!
See this thread for details.
Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Database design

 
Mike Anna
Ranch Hand
Posts: 117
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
Hope this is the right place for this.

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.

cheers,


 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34837
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mike,
Having too many record isn't a problem. You can partition the table by something (project id maybe) so queries can proceed as if there were multiple tables.
 
Mike Anna
Ranch Hand
Posts: 117
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
this is how my final jsp table shoul look like :
metric1 metric2 metric3
ProjAttrib1
ProjAttrib2
subProjAttrib2
projAttrib3

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.


cheers,
 
Mike Anna
Ranch Hand
Posts: 117
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic