Hi -
Very new to the idea of database design, so excuse any completely ignorant questions!

We're a very small organisation and about to try putting together our first software product. (we is me and a programmer who has a lot of experience with Java but not so much in database design).

The product, very briefly, will record all 'students' arriving at a 'school' (the reason for the inverted commas is that the actual environment is not a school but this is probably the closest simile that people will recognise), assign them to a 'teacher' and then record test results against them at various (and variable) times during the year. The main purpose of the product is to generate reports on the data gathered, as many and varied as possible (for example progress through the year, summaries of students, averages, comparisons from one year to the next, etc).

I have two main questions:

1 - I've been reading 'Database Design for Mere Mortals' by Michael Hernandez. In this he mentions that there are two different types of database design, Operational and Analytical, and that his book describes the design method only for Operational. From his brief description I'd say that the database application described above is probably more of an analytical one than an operational one (it certainly isn't really concerned with the general workflow of an organisation and is mainly concerned with analysis of recorded data rather than day to day management. Can anyone tell me if I'm right in this, and if so, is there a good alternative database design method I should be using?

2 - My programmer colleague believes he can create the database necessary within Java rather than using a dedicated RDBMS, for example through creating classes to stand in for tables, and so on. Are there pitfalls or major problems we would run into by going down this route? Is the fact that we would have to write the application for each report rather than relying on SQL queries going to restrict us? And is there an obvious way of mixing the two, so that the application can be written in Java, working with a good database package? Bear in mind that the program is intended to be downloaded to a large number of individual users, and we wouldn't want to be getting them to install SQL servers and other enablers on their machines.

Hope all that makes sense, for those of you still reading!

Regards, Steve

If you ever intend to make this a scalable Enterprise application, or you want to sell it to customers who will want to integrate the data with other applications and databases, then you should use an RDBMS.

ok thanks. Well, it's not really intended to be a commercial app (we're giving it away with membership of our community) but we'd like to do it properly.

Anybody got any ideas on the other question?

The two classes of database applications are more commonly referred to as OLTP (Online Transactional Processing) and OLAP (Online Analytical Processing).
For your application (like many) will include elements of both. You will be both recording information and reporting on it. I encourage you to design a database in 3rd normal form, and it will almost certainly satisfy your requirements.

Hi...
I don't exactly understand what your colleague means here. Probably not to create a database implementation from scratch. With java, you can use J2EE as a persistance solution, or a standard RDMS with JDBC drivers.
J2EE encapsulate all database management, so you can't easily access the underlying database if needed (from another application, for instance).
With JDBC, you can directly access databases with SQL queries. The best choice IMHO. If you want to make your code independant from database structure, you should consider using Hibernate or some kind of mapping software.
HTH...
Laurent