i am developing a library managment system using vb and oracle...
i want to provide queries so that user can see the books available,
no of copies of each books,and complex queries....
what is the best possible way in which i can provide queries without disclosing the underlying tables...aslo i am assume the user to know nothing abt oracle(queries) the interface should be simple and understandable and should provide the power of queries...
what is the reason for not showing underlying tables? in my experience, it is either an edict by the powers-that-be, or not wanting the users to need to know sql
generally speaking, you should give an sql interface only to geeks (and i mean that in a nice way)
non-geeks want something that lets them construct all kinds of different queries, but without having to know sql
table layouts, in this situation, can be very helpful
anyhow, the best models for this behaviour are web search engines
for example, google's advanced search has text input fields for word and phrase searching, and drop-down choices for Language, File Format, Date, Occurrences, and Domain, and a radio button for SafeSearch
if you were to build a screen like that, which fields would be of interest to your library users? title, author, copies? would these be text input fields, dropdowns, or radio buttons?
these form elements all map to fields in your database tables, and so you would take the user's choices and generate the sql dynamically
well i am still not very clear abt wht to do..the person i am providing this software has no knowledge abt sql....
the problem i am most concerned is how do i let the user specify the where clause which can sometimes inlcuderelating many tables...
can u plzz proivde me with a sample interface....
so the user enters some string into the "Search For" text field, and makes a choice from the following "Search In" dropdown list options --
then your interface generates the sql as follows:
select * from books
<IF SearchIn = "Title">
<ELSEIF SearchIn = "Author">
like '%' || SearchFor || '%'
so you dynamically choose which table field to test in the WHERE clause, based on the choice made for the dropdown, and also you put the wildcard characters around whatever they enter in the SearchFor text field
that's basically how you would do it
and of course if you have multiple fields, like the google advanced search page i showed you, your interface gets a bit more complicated