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.
