Re: Best way to represent values.

At 01:19 PM 11/21/2005 -0500, Dennis Veatch wrote:

>I had thought just adding some fields called topsoil_start/topsoil_end,
>gravel_start/gravel_end, etc. But them I'm left with how to take those values
>and give to total depth for each layer and total depth of the well.
>
>But I'm not sure that is the best way to handle this.
>
>Does anyone have some other suggestions?

I'm no DB guru, so I am probably a bit out of my depth here.

But how about something like:

create table well (
id serial,
name text,
created timestamp default null,
-- more fields probably follow - site, location, status etc
)

create table layers (
id serial,
well_id int,
layertype_id int,
end_depth int
)

create table layertype (
id serial,
layername text,
comment text
-- probably more fields
)

(you probably might want to add the foreign key constraints etc etc).

Basically you have table of wells.

And then you have lots of rows in layers that are linked to the same well
by well_id, and you sort them by the end depth.

And then you have a table of layertypes which each layer links to. So you
can create types of layers.

e.g.
select layername,startdepth from well,layers,layertype
where
well.name='somewell'
and
well_id=well.id
and
layertype.id=layertype_id
order by end_depth asc

I've no experience in wells but you might want an "Unknown" layertype to
fill in the gaps ;).

You might alternatively want to have "start depth" instead of an "end
depth". I'd do end depth, since your data probably ends at the deepest
layer (I assume you never reach the core ;) ).

You may need both start and end depths if there are multiple layers per
depth per well (nonuniform). In that case the queries could be a bit more
complex...

I might have overlooked a few pitfalls here and there. Oh well...

Good luck!

Link.

*runs and hides*