my dog learned polymorphism
The moose likes JDBC and Relational Databases and the fly likes Database design Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Database design" Watch "Database design" New topic

Database design

Mike Anna
Ranch Hand

Joined: Jul 08, 2007
Posts: 117
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.


Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Mike Anna
Ranch Hand

Joined: Jul 08, 2007
Posts: 117
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.

Mike Anna
Ranch Hand

Joined: Jul 08, 2007
Posts: 117
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.
I agree. Here's the link:
subject: Database design
It's not a secret anymore!