Do I need a server?

[kilimanjaro]

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.

[Gowator]

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 ....

[kilimanjaro]

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?

[Gowator]

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 :D

 

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.....

 

 

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.

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.