pcplantdb

[Richard Morris <webmaster@pfaf.org>]

Hi Folks,
        I've been giving a bit of though to the tables needed
to control the database. As well as the tables for the actual
data I see the need for a few tables to control the way tables
are linked etc.

As an example lets assume we have two databases pfaf and ethnobot say.

pfaf has two tables
Species Database:
Latin Name, Common Name, Habitat, Range, Edible use notes, ....
this has one entry per species.

Edible uses:
Latin Name, edible use type
This can have 0, 1, 2 or more entries per species giving a short
description of the use.

ethnobot (data from the ethnobotany database by Jim Duke) has two
tables
Common Name:
Latin Name, Common Name
Uses:
Latin Name, Use, Country

Problem 1: Different Latin Names are used.
Theres a great deal of difference between the names used
for particular plants, different authors will use different
synonyms. To make things worse theres also slight variations
in the gender endings used:
        Achillea millefolium
        Achillea millefolia
*us, *is, *e, are also common. Theres other slight spelling
changes ii -> i  , ll -> l etc.

What I think we need is a large table with every conceivable
latin name in the left hand column and matching latin names
for each database in the subsequent columns

Botanical Names cross reference:
Botanical name/synonym  pfaf latin name,        eth latin name
Achillea millefolium    Achillea millefolium    Achillea millefolia
Achillea millefolia     Achillea millefolium    Achillea millefolia
Achillea lanulosa       Achillea millefolium    Achillea millefolia


Problem 2: Handling users
Theres various levels of users
        Administrators  - full access to all tables/datasets
        Dataset owners  - full access to their tables
        Registered users - people who've gone through some
                           sort of registration process and in some
                           way trusted. Might be able to create
                           new tables, add column to existing
                           tables.
        Unregistered users - anonymous people on the web
I think we need some tables to handle these

User Info table:
Nickname        - a short name used to identify the user, for
                        example I might be pfaf
Full Name
email address(es)
web sites(s)
physical address
phone nos
security info   - whether they are willing to have name, email etc
                        displayed in web pages.
password        - needed when people want to modify things over
                        the web, this will be very insecure and
                        should be different to ibiblio password.
                        Anyone know about security side of things?
level           - admin/dataset owner/reguser/other

Database and table owners table:
database or table name
nickname        - refers to above table
permission      - owner/add row/add column/edit

(might want some special nicknames admin, owners, registered user, all)
to control permissions.

Problem 3: Recording who did what and when

For tables which can be altered by more than one user I think
each row should have two fields, nickname, modification data
which control who entered the info. So a general plant table
editable by all might be

Latin name, common names, habitat, ... , nickname, mod date

theres a bit of a problem if people are allowed to edit
a particular row. I'd suggest that in general people are only
allowed to add new rows and not modifying existing info.
There would be another editing process where data entered
could be combined, probably only done by trusted users.

Eck
        Still a few more thoughts but I'll save that for
anther post.

                see you all later

                        Rich

--
Plants for a Future: 7000 useful plants
Web:       http://www.pfaf.org/  or  http://www.comp.leeds.ac.uk/pfaf/
Main Site: Blagdon Cross, Ashwater, Beaworthy, Devon, EX21 5DF, England
           Tel: (+44 845) 458 4719
Email:     webmaster@pfaf.org (web related queries only)
PFAF electronic mailing list http://groups.yahoo.com/group/pfaf