On Tuesday 22 November 2005 11:40, Lincoln Yeoh wrote:
> 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*
>
Thanks everyone for the on-line and off-line suggestions. Now I just need to
sort through them.
--
You can tuna piano but you can't tune a fish.
