• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Database design

 
Ranch Hand
Posts: 117
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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,


 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic