Storing and Searching for Keywords in Table
obr...@cbr.hhcs.gov.au
problem in Paradox for Windows (4.5).
I want to make a database of book details with name, author,
ISBN etc but I also want keywords stored and thus be able to
search for them later.
I can think of 3 approaches :
1. Make a field for each keyword, say up to 8 and index on each field.
Disadvantages seem to be many wasted (blank) fields and the need to
search for the keyword in each of 8 fields. This would appear
inefficient, even if indexed.
2. Make one large field and put any keywords in it, separated by
spaces. Similarly there would be wasted capacity (full field size
not being used in most cases). Search within keyword field using
Paradox functions.
3. Similar to 2. but write custom C++ code to search on the field for
the presence of a keyword. This MAY be more efficient in
execution, but would take time to write and debug.
Is any of these methods preferable or can someone suggest a better
approach.
Thanks,
Eddie O'Brien
J. Wakeley Purple
: ISBN etc but I also want keywords stored and thus be able to
This sounds like a good place to implement what is called 'normalizing'
your database. You have (I hope) a key for the main table.
Make a 'child' table with some unique key, could be just a number.
Include a field that will take the key from the master table (maybe ISBN
#?), make a secondary index on this field (faster that way), the 3rd
field will be Keyword.
Then combine the two in a form using the data model, linking the common
fields. Remember to right-click the child table and unset the ReadOnly
property, also make it AutoAppend.
One way to do this is to let the keyword be the key for the child table.
That should minimize the size of the child table. That will allow a
search for keyword and give all the references to that keyword. You can
also arrange it so you can see which keywords are used for a certain book.
Lots of possibilities...
I'm talking off the top of my head, read the manual to be sure.
--
J. Wakeley Purple - wa...@iglou.com
Nick Moon
>problem in Paradox for Windows (4.5).
>
>I want to make a database of book details with name, author,
>ISBN etc but I also want keywords stored and thus be able to
>search for them later.
[snip]
The correct relational database approach would be two tables. One table,
indexed on say the ISBN, contains info on the book,(Title,
Author,Publisher etc).
The second table would consist of ISBN field and the keyword. This way
you could have as many or as few keywords as you like. Also, by
maintaining a secondary index on the keywords, you can do searches for a
given keyword much faster.
However, I am sure there are other products on the market, directly
intended for this kind of data retrieval.
Cheers
Nick.
D.A. Welch
...
>The correct relational database approach would be two tables. One table,
>indexed on say the ISBN, contains info on the book,(Title,
>Author,Publisher etc).
>The second table would consist of ISBN field and the keyword. This way
>you could have as many or as few keywords as you like. Also, by
>maintaining a secondary index on the keywords, you can do searches for a
>given keyword much faster.
>Cheers
>Nick.
I have a database with the structure that you have described and have
a form which displays each master record and a table view of the Keywords
associated with that record. However, a search (either ctrl-z, or OPAL
locate/locateNext) only searches the currently displayed set of keywords,
it does not search the entire keyword file displaying each master record which
has the specified keyword associated with it. What am I missing?
Dwight Welch Dwight...@USask.Ca
Database Manager-Canadian Cooperative Wildlife Health Centre-Saskatoon Canada
Mathias Jonsson
> From: obr...@cbr.hhcs.gov.au
> Organization: Health, Housing, Local Government and Community
> Services
> I would be grateful for suggestions about solving the following
> problem in Paradox for Windows (4.5).
> I want to make a database of book details with name, author,
> ISBN etc but I also want keywords stored and thus be able to
> search for them later.
> I can think of 3 approaches :
> 1. Make a field for each keyword, say up to 8 and index on each
> field.
> Disadvantages seem to be many wasted (blank) fields and the
> need to
> search for the keyword in each of 8 fields. This would appear
> inefficient, even if indexed.
> 2. Make one large field and put any keywords in it, separated by
> spaces. Similarly there would be wasted capacity (full field
> size
> not being used in most cases). Search within keyword field
> using
> Paradox functions.
> 3. Similar to 2. but write custom C++ code to search on the
> field for
> the presence of a keyword. This MAY be more efficient in
> execution, but would take time to write and debug.
> Is any of these methods preferable or can someone suggest a
> better
> approach.
> Thanks,
> Eddie O'Brien
Use Paradox as a relational database instead. Create two tables:
Book.db whith info about the book as ISBN, name, author etc. and an
unique ID for each book. Use that as a primary key.
Keyword.db whith two fields: The ID field as in Book.db and a keyword field as
a string. The index of this is both fields.
Now you can fill in as many keywords as you want to by using the keyword.db and
just specify the right ID.
This is much automated if you link them in the forms datamodel.
Good luck! Read the introduction and the sample application delivered with your
PFW for more info about related tables.
//Mathias Jonsson, BasData
--
|SLink : Mathias Jonsson 30:30/107
|Internet: mathias...@basdata.slink.se
|
| Standard disclaimer: The views of this user are strictly his own.
Mathias Jonsson
> Subject: Re: Storing and Searching for Keywords in Table
> Organization: University of Saskatchewan
>>Nick.
OK do like this then:
Use the first table with book information and an Book_ID.
Use a second table with keywords and another Key_ID.
Use a third table (COMP) with two Book_ID and Key_ID fields just. Let this
table have a composite index using both of the fields for the index.
Link it as BOOK -= COMP - KEY when you want to look at the keys for a specific
book.
Link it as KEY -= COMP - BOOK when you want to look at the books for a specific
key.
You will also need a way to insert new key in the key table.
By doing it this way you can use existing keywords and add others if needed.
Every keyword will only be saved in one place.
This approach is a bit more complex than before, but I think it will be a
intuitive implementation.
Hope you understand what I mean?
Nick Moon
>The correct relational database approach would be two tables. One
table, >>indexed on say the ISBN, contains info on the
book,(Title, >>Author,Publisher etc). >>The second table would consist of
ISBN field and the keyword. This way >>you could have as many or as few
keywords as you like. Also, by >>maintaining a secondary index on the
keywords, you can do searches for a >>given keyword much faster.
>...
>...have a database with the structure that you have described and
Keywords
>associated with that record. However, a search (either ctrl-z, or OPAL
>locate/locateNext) only searches the currently displayed set of keywords,
>it does not search the entire keyword file displaying each master record
>which has the specified keyword associated with it. What am I missing?
>
>Dwight Welch
>Dwight...@USask.Ca
Dear Dwight, (& Eddie who started this thread if your still following)
The behaviour you describe is correct. When in a TableFrame, or similar,
Paradox limits the view of the underlying tables, to only those records
where the key matches, the primary record. Hmm, that's a bit of a
mouthful. Try again, Nick.
The primary table, (books keyed by ISBN for example), has one currently
selected record, with ISBN of say 'ISBN 0-7181-4002-8'. The form's data
model has this table 'BOOKS' linked to a secondary table of 'KEYWORDS'.
This link is a one-to-many link.(a book ISBN can link to many keywords).
The table frame in the form only shows those records in the 'KEYWORDS'
table, which have the same ISBN i.e 0-7181-4002-8. Really Paradox is
being quite clever.
Now for some fun & games. You could create a new form, with the
'KEYWORDS' table as the primary table. and then link to the BOOKS table
in the opposite direction. (You may have to make some field a key if it
isn't). I would show the primary records in a tableframe, and link to
another table frame. Then you could scroll through a list of keywords,
and all the books, that have that keyword associated with them will
appear in the detail tableframe.
Of course, that probably isn't really what you want. So the next stage is
to setup a query, where you search for a match, to combinations of
keywords. Queries are not my strong point though. I would have to go away
and setup a test table, before I open my mouth on that subject. However,
you should be able to setup a search for all books that have say the
words 'CAT' OR 'KITTEN' OR 'DOG' OR 'BITCH', and also have the word
'RAINING' in.
Cheers,
Nick.
Brian Clark
>
>I would be grateful for suggestions about solving the following
>problem in Paradox for Windows (4.5).
>
>I want to make a database of book details with name, author,
>ISBN etc but I also want keywords stored and thus be able to
>search for them later.
>
How about having a seperate table with the keywords and an index to where
they can be found in the major table(s).
Good Luck
OLIVER NEUMANN
->I can think of 3 approaches :
->1. Make a field for each keyword, say up to 8 and index on each field.
-> Disadvantages seem to be many wasted (blank) fields and the need to
-> search for the keyword in each of 8 fields. This would appear
-> inefficient, even if indexed.
->2. Make one large field and put any keywords in it, separated by
-> spaces. Similarly there would be wasted capacity (full field size
-> not being used in most cases). Search within keyword field using
-> Paradox functions.
->3. Similar to 2. but write custom C++ code to search on the field for
-> the presence of a keyword. This MAY be more efficient in
-> execution, but would take time to write and debug.
4. Create a Memofield with M1 and search for:
proc search()
var
TC TCursor
endvar
TC.attach(Field)
if TC.locatepattern("Fieldname",".."+Locatefield+"..") then
Field.resync(TC)
else
message(string(Locatefield.value)+" not found")
endif
endproc
Oliver
ma...@levels.unisa.edu.au
ALL terribly inefficient compared to normalising the data!
Place 2 fields in a new table called KEYWORDS with the contents of the
fields being 'Parent ID' and Keyword.
Place a maintained secondary index on the KEYWORD field and search on it
for the keyword of interest; if found then search the parent table for the
id - simple and speedy and normalised.
--
Raymond Kennington | Act in haste and repent at leisure
| Code too soon and debug forever
University of South Australia | ....Dale and Lily (I think)
Computer and Information Science | Knobs, knobs everywhere,
Levels | just vary a knob to think!
Pooraka 5095 | ....Metamagical Themas, Hofsta"dter
South Australia
ir...@staff.monash.edu.au
>Subject: Re: Storing and Searching for Keywords in Table
>>I would be grateful for suggestions about solving the following
>>problem in Paradox for Windows (4.5).
>>
>>I want to make a database of book details with name, author,
>>ISBN etc but I also want keywords stored and thus be able to
>>search for them later.
>[snip]
>The correct relational database approach would be two tables. One table,
>indexed on say the ISBN, contains info on the book,(Title,
>Author,Publisher etc).
>The second table would consist of ISBN field and the keyword. This way
>you could have as many or as few keywords as you like. Also, by
>maintaining a secondary index on the keywords, you can do searches for a
>given keyword much faster.
Just a couple words here: if relational means anything, do not store the
author together with the title. What this means is that an author can have
only one title and viceversa. Unless you make them non-key, of course, and
repeat fields.... Same with publishers.
Another observation: in my experience, ISBN numbers are a bad key, unless you
allow for duplicates. They may have been meant to be unique, back in
prehistory, but the reality is that they are not.
Regards
Iris.Ra...@Lib.Monash.edu.au
(disregard the official signature - another thing that's broken!)
Nigel J Rheam
[...]
>
> Just a couple words here: if relational means anything, do not store the
> author together with the title. What this means is that an author can have
> only one title and viceversa. Unless you make them non-key, of course, and
> repeat fields.... Same with publishers.
>
But for Authors, I believe the convention is 'Author 1 and Author 2 and
others'. So if you did not want to add another table ('de-normalising' is the
phrase, I think) you could simply have two author fields, and a third 'flag'
field to indicate if there are more than two authors.
A problem with this solution is that it makes it harder to search on the
author; so it may not be worth your while.
Have fun!
Nigel J Rheam