Re: Design Questions

alex_at_aloss.de (Alex) wrote in message news:<61c1038d.0107050619.76b60c39_at_posting.google.com>...
> Hi!
>
> I've a table with measured values from nmr spectroscopy. For each
> measurement there are rows like this:
>
> id structure values
> ----------------------
> 1 1 3.5
> 2 1 3.6
> 3 1 4.2
> 4 1 5.2
> 5 1 7.5
> 6 2 4.2
> 7 2 6.3
> 8 2 8.2
>
> In my database there are about 40 values per structure. With having
> the nmr data in the database I need an efficient solution for the
> following retrieval task.
>
> Find the structures which have the values x1 .. xn, tolerance=d
> so for each xn the structure can hold a value that matches 0-100%
>
> At the moment my result work with php array and n selects to my
> database which is indeed not efficient.
>
> Does anybody has an idea how I can perform a better search?
> Maybe there is a better way to store the values?
>
>
> Thanks
>
> Alex

Your example does not include the 'tolerance' column. In order to design a structure, all relavent fields or columns need to be known. So far, excluding tolerences, I'd suggest and main table with Structure as the primary key. The child table would have structure as a foreign key linked to structure in the main table. The combination between the structure and value could create the primary key for the child table. Making sure there are indexes on all these fields will greatly enhance the search. In your query you would do something like

select maintable.structure, childtable.value from maintable, childtable where value between [firstvariable] and [secondvariable]; This is assuming your values column is a double or number with precision (the name varies based on the software you are using). I'm sure your example makes sense to you the user, however, based on what you want to find, I don't understand it. You mention % but nowhere in your column are you using percents, you mention tolerance. Also, what's nmr?

If you can explain what you want in layman's terms, I'll gladly give my two cents. Received on Thu Jul 12 2001 - 00:14:52 CEST