help with first database

sc_biologist · #1 (**permalink**) 09-19-04, 18:36

I'm a biologist with minimal programming experience, tasked with trying to build a database to keep track of a large amount of temperature data.

Briefly the data can be categorized by site (~20), and subsite within site (5-20). Within each subsite, we have temperature data collected at 10 minute intervals for up to 6 years. The data are currently stored in separate .csv files covering ~6 months of a subsite.

We need to build a database that will let us search the data by site and subsite names or attributes, and date/time intervals; and then save the searches in .csv file to use with other programs. Secondary goals are to provide web-access and make the database directly accessable by other programs, particularly the stats program SAS (which I think can handle ODBC querys?) I have a choice of two computers: a Windows box or a Mac running OS X.

Here are the constraints:
I have limited programming skills and not much time to learn. I'm leaving this job in 6 months and my boss has less interest/time for database management. We also have a budget of ~$200 for software.

I've been looking at Filemaker, because it seems to have an idiot proof interface and reasonable academic pricing, but I'm not sure if it can handle this much data. I think our university has a site license for Oracle, but I've never used it.

Suggestions on software and/or tutorial websites would be great.

Thanks in advance,

Sarah

Littlefoot · #2 (**permalink**) 09-23-04, 04:13

As your university already has Oracle, I guess it would be OK if you really use it.
Primary goal - design of the database - doesn't seem to be too complicated. First idea is to create three tables to store a) site data, b) subsite data, c) temperature data. Just as a first aid, try something like this:

Code:

create table site
(site_id number(3),
 site_name varchar2(30));

create table subsite
(sub_id number(3),
 sub_name varchar2(30));

create table temperature
(site_id number(3),
 sub_id number(3),
 time_stamp date,
 temp_value number(5, 2));

Please note that this is just a scratch - I believe real model will be much richer in description, number of tables and fields within. Furthermore, I didn't create constraints, indexes etc. just to keep it simple. Read more about Oracle SQL here, while all books index can be found here.

How are you going to fill data into the tables? "Site" and "Subsite" are easy - you fill them once and that's it. But, what about "Temperature"? You'll have an operater whose job will be to do it? I guess not! Have data delivered from the subsite and then loaded into your table(s)?

Querying this tables is quite simple; I'm not familiar with the .csv extension, but if it is a flat text file, spooling results of the query would do it.

Secondary goals ... well, I'm afraid I can't help much you about this. If you use Oracle Developer Suite 9i, which is web-oriented (so forms and reports are run and displayed through internet browser), I guess it wouldn't be too complicated to have that kind of a web access. And SAS ... I really don't know anything about it, but - if it uses ODBC - there shouldn't be too many problems.

If I may add to this ... no Oracle experience, 6 months deadline and budget of $200 smell like a nightmare to me. Good luck, anyway. You'll need it

P.S. Let us know about the progress of your project. Could be interesting to participate in it, even though by the mean of this Forum. There are many DB wizards here which will be happy to help you with the project.

Internet Services

Web Development

Digital Marketing

Consumer Tech