At work we are building a data base for all of our specimens, and we have three computers networked together. I don't know anything about servers or networks, but I was thinking that it might be easier and more efficient to get another computer to act as a sever for the database. In about a year we are going to be expanding our system, by probably 5 more computers. The computer that actually stores the info is also being used normally, and works slower becuase of it, I just added 512 more ram and it is still slower than the other two computers., but it also runs the scanner and the printer. All of these systems are microshaft, and I doubt my boss will be willing to switch, but the campus server is linux, so I thought our server could also.

Any thoughts, suggestions, or ideas?

I wouldn't definitely look into getting a Server. Make sure the printer and scanner can work with Linux. Be sure to know how to work with Samba, and also look into finding some sort of interface for the database (perhaps web-based).

What kind of a system do I need for a server? The printer and scanner can stay on the current machine. What is samba? Will the microshaft systems they insist on using work with a linux server? Can the server be used as a router/firewall for the other workstations? Can I use the server as a standard computer while I am in the office instead of the microshaft systems? And the data based needs to be accessable on the web>

A server is no different to any other workstation ... except how you use it.

For instance you might decide it doesnt need a screen. keyboard etc. which free up respources for X ....

As a database server in theory it shouldnt make any difference but in practice if you are developing a database then its easier to develop in the environment you want to work in later.

You can use 1 ,2 tier or 3 tier
2 tier
DATABASE -------------APPLICATION ON WORKSTATION
3 tier
DATABASE -------APP SERVER-----WORKSTATION

1 tier all on one!

Its a matter of size of database and where its accessed from etc.
If its academic use you might get free Oracle... which rocks... if not mysql is very good... both are in a different league to MS SQL server...

Anyway, what samples, how many does it involve photo's/photomicrographs/etc.

How do you wanna query, by web form or dedicated application etc.

looks like a server would be better.
Linux is clearly the best on servers.
The trouble is that if you choose a M$ server, you have the right to be wrong. If you choose a Linux server, not.
So better be sure.
The best way imho, at least it's how I did, is if you have a M$ laptop computer and a Linux desktop at home, you test everything at home before. So almost no risk. And no hurry. Time is money and is the trouble with computer: when you change something, you know when you start, you never know when it will be finish. So if you don't want to spend nights and week-end at work ......
Edit:

This data base is for the plant collection at the herbarium where I work, it is basically just text and it needs to be accessable on the web, open to the public, and from the herbarium computers, unfortunatly they are using microsoft access for the database, will a linux server beable to use the data form access?

I guess what I am really asking is: will it make it easier overall to have a dedicated server? And wIll a linux server create more problems than it solves?

It might create a few probs with MS access ...

however maybe they want to think about what goes into a customer database and where it should be relative to 'confidential data' like stock prices etc. (should it be in a DMZ?)


linking via ODBC is the best bet ... however I havent done this none MS to MS.. (i.e I once did it with .asp )
however the access route really is a big trap... Im not just saying that but if/when it expands things get nasty.

I used to use access a lot btw... but I find in every case it ends up creating more work ....

think like this, when the database is new its easy to prototype and change stuff and access makes this even easier... however once its full of data and been used in certain ways its harder to change so what happens is you build all this into Access and the way its built as an app makes it hard to port it elsewhere after.

The access DB might be working fine internally but if it were used with thousands of customers and starts growing it might be different. Think like this board has a mysql DB behind it and how fast it generates the info on searches etc.

when the board was new the DB was prob tiny (ask anon I guess but basically a few KB) now its prob grown to giga's ... and this is just a forum.

I wouldn't trust access with 10+ users also the more data the worse it gets.

For your needs linux running apache, mysql(or pgsql) and php with webforms for data entry and views.
Will handle many connections with ease.
You have to decide if it is worth it to have a stable robust solution with initial significant learning investment.

FYI my workstation also hosts an intranet search app thats biggest table has over 10 million rows and i don't notice when it is in use.

Agreed, but Kilimanjaro is going to need to convince the shop of this.

Access typifies MS.... Its easy to start off and looks nice at first then as you try and really use it it falls apart.

However many companies are only using the most minimal parts of it with a few users and dont undrstand the scale up issues.

It looks nice and its doing a job so they try and modify it instead of a clean start.

Big mistake in my experiecne but hard to stop!

Is there any easy way to take the data from access and transfer it to a different setup?

Well you can export tables in access to csv format then import into mysql tables using webmin.
Webmin needs tab seperated format to work so you can convert the csv to that format on any decent spreadsheet program even most text editors (find and replace , with a tab).

There is a comercial program that can export from access to mysql server directly but i forgot what it is called, there is a free trial but the software only runs on windows.

Another way is using ODBC except I think it has to be done from Access since I dont think access is ODBC complient (you cant connect to it)
I might be wrong on this part or it might have changed!

Anyway this works with Oracle and Access so should work with mysql too.
Make a new empty database with a test table to connect to.
Connect via odbc and read the table ...as a test
create table with the same structure
make sure user has create and write perms in mysql

append to the table you created....

I doubt this is quicker than Johnnyv's method tho!
Doing this tho allows access to be used as a FORM in the interim by replacing the tables with linked tables which are in mysql!

You would need a mysql ODBC driver for Windows!

I have no idea what you are saying to me. I found that OO has a database function, and OO can use microsoft formats, so I wonder if if can use access?

What do I need for the processor, ram, hardrive(s), network connection for the server? The workstation systems we have are 1.8 gh processor, 512 ddr, and 40 gb hardrives. Should the server be more powerful, or would this be sufficient, but with maybe a bigger harddrive?

I guess that would depend on how much data, and/or how fast of a system that you need to work efficiently. I would definitely find a decent motherboard that can be upgraded at a later date if the need arises. I just completed a mini-server for my home network with the following...

S2466n Tyan motherboard
2 x 1ghz amd cpu's
only 256mb of memeory currently
and an ATA100 hard drive 7200rpm 40gb.

Plenty of room for upgrades if I decide to add a few hard drives, faster cpu and memory. It's quite a bit of overkill for what I'm am going to be using it for, but if I decide to start an e-mail server, or something of that nature, I have something that can be upgraded to meet my needs.

Flex

The datbase I am working with will eventually have the information for a 175,000 + plant specimen and be accessable through the internet to anybody who wants to access it.

ODBC = open database connectivity. and true to form MS can read it but not be read by it!

Its a compatibility layer so mysql can write to a Oracle database etc.

You have lots of options, that possibly the problem.
For instance a simple php form would probably serve client queries/displays, a more detailed one internal queries but you might need something more detailed for data entry/admin.
In oracle terms you use triggers etc. to make a certain event happen if conditions are met.
Its just like adding a if... then... else onto a field.

You chose where you add these for iunstance to the field itself or to a form for the field only.

more later... off to work

OK more time now...

175,000 records shouldnt be much of a challenge to a real db like mysql.
You might want to see how the database itself will look in terms of drawing up the main tables and what they will hold and look into php and/or PERL:DBD to access.

As far as accessing it is concerned what I was saying is if you use linked ODBS tables for Access then the forms can stay the same internally for now letting you work on migrating the data and web interface!

You need this odbc connector(s) mysql to connect so access can read/write the tables in mysql....

Its an interim migration step IMHO not suitable for long term use but its a good sideways step away from having the data and forms locked in the same file.

I'm going to set up a database on my computer and see if I can start transfering data from the work computers.

Has anyone had any experience with the OpenOffice database?

Thanks for all of your help!

OpenOffice as far as i know doesnt have a database
it does have connectivity to databases but I think only StarOffice has an actual database integrated. adabas


However the choice of database is important to a point.

That is 175,000 records is reasonable, perhaps even small for mysql but large for something like Access. Its hard to explain why without lots of DBA tech lingo.. but they are just designed differently. One is the consumer model SUV with road tyres and leather seats and the other is the utilitarian land cruiser/land rover with bare insides but 100% designed as an off road tool!

Sure the SUV looks nice, easy to drive and stuff but the minute you actually take it seriously off road you'll wish you had the serious stuff!

You should start out by sketching the tables and working out relationships.
Like will it be taxonomy based or environment based? Would you present plants by where they grow, soil conditions (or water) flowering, biennials and perennials etc.

Will you use the database for clients to order and keep client details related to purchases and even details such as their gardens?

How about stock control, will you keep stock numbers int he database etc.

All of this will give you an idea of how many tables and how many relationships between them and also how you wanna present this data (php forms etc.)

If I were doing this I would probably choose mysql...
This is partly bias becuase i know Oracle well and mysql is quite similar but also its more standard so you can use webmin etc. to admin it.


Bah, I just found this which says it better than I can!
migrating from MS dbs to MySQL

Based on your specs I'd say Access is a bad choice. It is made to be a simple easy to use database for 1 person on 1 computer. You can push it past that but it gets hairy. Using Access for a web database is like moving your house with a motorcycle, you can get a side car for it but that doesn't make it a moving van.

The advantage to access is that it is easy to make a database and make an interface for one person to use while he's at that computer.
It is hard to make that database available on the web. That's where a more powerful database system comes in. What you want is an SQL database.

What you want is mysql on linux. it's free. As an SQL database there is a learning curve. you need to know SQL to talk ito it.
Instead of using the graphical interface to define data fields and display them like with access you need to do it with SQL, HTML and PHP in a text editor and a terminal. Yes, there are graphical programs that work like kind of like Access. The pay off is that once you get the hang of it it is very straight forward and you can make it do ANYTHING you need it to do. Put in on the web, make it a firewall/gateway for your office, have varying levels of security for different users in different locations, etc.

Get a copy of "teach yourself SQL in 24hrs", a mysql dummies book, or go through a tutorial on the web. There are probably many examples of something like what you are trying to do already online. free HTML and PHP code, database samples, tutorials, etc. look around.

As for the machine, any $200 used PC will do the job. If it is going to sit in the corner and be a server then a pentium2 with 64 MB and a cheap ethernet card is all you need, seriously. There may be one sitting unused in the basement. Spend the money on a big, new, fast, hard drive. With a database that's where all the work happens. You'll need a little more juice for a workstation.

If you can't talk them into linux at least talk them out of access - make them go with MS SQL server or Informix or something. Access is OK for simple stuff, for 1 or 2 people and a couple thousand records, anything more and you are in for a headache.

Agreed 100% plus You can always post up here for help on SQL syntax
Indeed you can even just use Access to make a query then look at it in SQL mode.
Access uses some non -ANSI SQL (surprise surprise) and the inner joins and things are weird but the best way is a bit of trial and error and post if you get stuck....


also check out the link it shows how to do a conversion and why you should and even lists a few cases where to use Access instead! (mainly single user on a single PC)

I found something called opengroupware0 and I assumed it was an OO database system.


Name: opengroupware-database
Version: 1.0-20040204.1mdk
Size: 759 KB
Medium: contrib
Currently installed version: (none)

Summary: OpenGroupware.org Database API

Description: This package contains the OpenGroupware.org database API.

From the top of my head opengroupware is a php front end for collaborative work.

when talking about databases its good to define the engine (background programme) and the schema (layout of tables and relationshiops etc.)

For Access the engine is called MsJet : others are Oracle and mysql.
The problem (or a problem) with access is the fact these are all mixed together in the mdb file.

I'd really recommend like grc says getting a basis in SQL and then choose how to access this (php/[perl etc.)

It sounds intimidating but its not hard really and i can help ...

I'd agree with that. SQL and mysql does look scary (complicated) at first but it is really not that hard. You'll find the UNIX stuff is very straight forward. None of that "why didn't it do what the instructions said it would do?" stuff you get with MS . And once you set it up right it just goes and goes. The almost complete lack of "the server is down again" headaches is worth the short learning curve.

everything you need is here:
http://dev.mysql.com/doc/mysql/en/index.html

I am will ing to give it a shot. I have download mysql , but I am going camping for the weekend, so I'm not going to mess with it until Monday.

There are alot of relationships set in access currently, you can search by: species name, genus, family, collector, habitat, county, and about 10 other catagories. Will numerous relationships cause problems in the future with access?

Try the primer becuase thats not quite right for a relationship
the question is if they are sort keys I think ...
the answer is they dont have to be keyd to sort and for a small database it might not make much speed difference anyway.

All that will happen is php/perl will send a query like
select species name, genus, family, collector, habitat, county from plant_table where habitat like '%ACID%' and family = 'hedera' and name like 'poision*'
order by collector,county decending;

you can play about in Access by doing drag n drop queries then select the mysql box!

Also dont forget the link i gave for the ODBC driver... it will let you mix n match as your developing ...

Im going to Amsterdam this weekend .. I dont think you will want me answering any technical questions!

(anna= see you this w'end then)

I downloaded a book "Learn MySQL in 21 days" I did day 1 this morning. Sometimes I almost want to change my area of study to computers instead of ecology.

cool where from I might read it myself!

One thing I didn't see from skimming this topic is how does the end user interface with the database? Is it a search system that can be satisified by simple queries? Or is it the type of search that requires the end user to know SQL and be able to create ad-hoc queries? How is data entered? How is it presented?

All these questions are just as important as trying to decide which database engine to use.

Another thing to keep in mind, is if your department develops this, you can develop it to keep the different layeres separate and non-dependant on each other. Here's an example:

If you use access to connect to the server through the background and develop screens and reports, you have developed a database plus an interface through access. Now you want to change your database to a different engine, you are stuck with changing the interface as well.

If you separate the interface from the database (SQL is supported by almost all database engines so this is possible), you can develop the interface and have it call the database engine using standard SQL to get yoru results.

Additionally, if you were really doing a bigger system, you would have a middle area which could be business logic. When I say business, I mean the logic that handles what is actually happening.

So, at work for example:

I would develop the interface for a web browser (very little decision making code inside, zero database). This area just calls the business engine (any server or local software can be the business engine).

The business engine makes all the decisions. Oh, user clicked on report, well lets gather the info and pass the report data back to the interface.

The database just sits there, it holds the data in an organized format. In some cases, for heavy demand systems, it too may have business logic stored within the database (doesn't sound like your requriements)

So you have 3 independant systems that can be swapped out. Let's say someone wants to provide a different type of interface with a different business engine but that accesses the same database, can do!

Something to stress with the department is that by going linux on the server, and using a web interface, you can support any type of end user computer. You can also provide server software, databases, and any unforseen server tools without spending money. Moneys is always a big point..

MadDog is right and this is something you should address... how will the users retreive the data and will all users retreive the same data...will some 'power' users populate the db and run adhoc queries like for instance do peoples planting habits react to thre economy or weather.
i dunno but these are the business queries as opposed to end user web queries and might require a deeper level of query like adhoc SQL....

You mention the existing Access db, what are people using it for, doing with it?

just a few points becuase like MadDog says this is as important as how you choose a engine.

AHHHHHHHHH! I don't understand! Too many questions...must go watch television to numb my mind.

I don't really know enough to answer these questions.

The current database is accessable from the web here:

http://www.sci.uidaho.edu/biosci/herbarium/database.asp

I'm not terribly impressed.

If you enter "Baker" under collectors last name you can see how it displays the data.

Gowator: I got the "Learn MySQL in 21 days" off of gnuttella, I could probably send you a copy.

well that looks very simple, a single table unless collector is given a seperate table?


Now just for example (im not saying do this) but habitat could be a lookup value or at least be built by modifiers like hypersaline, acid soil etc...etc..
this would make it easier to search on ketywords than free text...

Collection day can be stored as a date instead of 3 seperate fields...

Do you ever use the USGS township locations or the DMS location ? Should you do away with one or the other?

These questions are on the design of the table(s), do you want a big non relational (giant spreadsheet) or do you want the more structured approach where collector is pulled from a drop down list.
The latter gives more control and prevents misspelling but someone has to 'create' the user. This implies a person or persons with administration privs (they can create new users)

this is how the interface might be different for a web user and a data entry person ....

I think the current table is not very user friendly, I would deffinitely want a keyword, habitat, and township and range, the only problem is there is little standardization in the information on each specimen. The older ones just have a date and collector (1800s), some of the newer ones have location by landmark and a general description of the habitat. Newer ones have township and range.

I would also like to be able to search by associate species, but a problem with access is that when you do a query with associations it gives you a separate entry for each association, so if you have one entry for a species, but it has 12 associates listed, you get 12 records for the one plant. It can be confusing.

The "learn Mysql in 21 days" is only 2.7 mb I could email it to you, if you would like a copy.

I have a question about transfering data from access, could I put it all into a spreadsheet and then just enter it into my database somehow?

yep Ill pm you my email

never hurts to RTFM and so far Ive used mysql thru my knowldge of Oracle.

For the export it depends on how many tables?
A XLS file can have 32768 rows (I think) so thast might be a restriction but you can always also just use a csv...

However the odbc is the 'best' option since you can have a live copy of the data.
You can even move all the data into mysql and leave the current access DB connected to it for data.

(you just create a link instead of importing)

this might be good to keep the pressure off. If you dont impliment something on the new db people can continue using the old one. Also you can phase in new features

Creating a select list is quite easy...
at the simplest
create table v_collectors as select distinct collector from hugetable;

note distinct, this returns just one row per collector and you are making a table called v_collectors with the results of this query so it will have the same type (char) and length ...

I stuck the v_ so you can see its a validation table.

you might want to then add 2 new columns such as valid_from and valid_to
this can be used to say show collectors from 1800-1900 or >2000 or still valid_to is null.

these are just ideas.....



Hmm im not fuly undersanding that.. do you mean aswsocuiation by taxa ... i.e you put hedera* and it comes back with every type of ivy ? when you just want a list or do you mean associated as in by environment....

i.e. low light, alkaline soil ? and you then want other species which like the same environment to live in?

I'd certainly drop the DMS _and_ decimal degrees... it can be calculated on the fly on input ... i.e. dec degrees = deg + min*60+sec*3600

(Technically a location with just degrees can't exist since it must have a projection, datum and spheroid but for the purpose you have I wouldnt worry.. errors should be only a few kilometers )

Since you might conceivably want to display this on a map later its good to have one and have it consistent... and controlling dms is harder than decimal degrees (i.e. you can make a post input control that says the location must be within a certain box)

Is this the _only_ table in the database, do you have the field definitions its in tools/anylise/tables or such!

Its important to get this part right ... as it will be the usability of the db.

imagine you want to make the data input as easy as possible... you could make it so that only extanct collectors were shown to enter data! whereas in the search you could show them all.

its little things like this that make the database more pleasant to work with.