NYCPHP Meetup

[Hans Zaunere]

--- "Joshua S. Freeman" <jfreeman at amnh.org> wrote:
> First, thanks to everyone who answered my last salvo of questions.  I look
> forward to moving down to NY this summer so I can start meeting you in
> person at NYPHP meetings...
> 
> 
> This one is more of a MySQL question than a PHP question but here goes..
> 
> I'm creating a LAMP app. for a scientist who needs to gather a crapload of
> information about a collection of scientific specimens.
> 
> The main table ('specimens') has 61 fields.
> 
> For fields such as 'species_id' what will go in there is simply the id
> number referencing information from a lookup table containing 20 or 30
> species names, each with an id number.
> 
> (species_id smallint(6) NOT NULL default '0')
> 
> For fields such as 'priority' or 'condition' I'm thinking of using type
> 'enum':
> 
> priority enum('1','2','3','4') NOT NULL default '1',
> gen_condition enum('good','fair','poor','very poor') NOT NULL default
> 'good'...
> 
> Is this alright?..

Well, knowing how scientists (I don't know any, but watch the Science
Channel) they will ineveitably add 'very good', 'average', and so on.  While
this isn't nessecarily a bad thing, it is ineveitable.

> or.. should I do the same thing with 'priority' and 'gen_condition' that I
> do with 'species_id' ?...

Personally, I would.  I've played with the enum and set column types, and
always ended up going back to regular columns for various reasons (using
certain functions on columns, getting the values of a particular column, more
flexibility with joins, etc).  That said, set or enum columns certainly
aren't verboten, IMHO.

> An additional question:
> 
> there are NUMEROUS questions in the survey that ask the surveyor to choose
> between 'high - moderate - low' ... in reference to various different
> parameters in the survey.
> 
> do i have just ONE table that looks up 'high', 'moderate', 'low' (like the
> species lookup table which has two fields- species_id and species_name) or
> do I make different lookup tables for 'high', 'moderate', 'low'... once for
> each question in the survey asking "how much incidence of blah-blah do you
> see ? high? medium? low?"

I would go with just a single table.

Additionally, when I have a lot of options to select from, and the data is
all generally the same, I like to use just a single generic table, consisting
of an auto_increment id, possibly timestamp when the choice was added,
another int field to determine type of the row (a species, rating, etc) and
the value itself.  When delivering some research software for NYUCD I use a
single table to over 30+ types of oddball info and it worked very nicely.

Best,

H


> 
> I hope my questions are clear.  I look forward to some guidance.
> 
> Thanks!
> 
> J.
> 
> 
> 
> --- Unsubscribe at http://nyphp.org/list/ ---
> 
>