Page 1 of 1

Storing user searches

Posted: Wed Mar 07, 2007 3:46 pm
by ed209
I've never really done this before so I'm looking for advice on what the best way might be to store user searches.

The items being searched for are artworks. A typical artwork has various fields associated with it like:

'colour' e.g. blue
'theme' e.g. animals
'style' e.g. abstract

each artwork also has a description field e.g. "oil on canvas portrait of my dog sitting in a chair" (inputted by the artist)
and a keywords field e.g. dog, chair, portrait (inputted by the artist)

So when searching for an artwork you can select from a dropdown list for colour, theme and style but also search with user inputted string that searches against keywords and description.

So an example search might look like:

colour = green, theme = portraits, style = dada, keywords = grey hound dog in a chair

How could I store that data in a meaningful way?

I'm thinking of using 2 tables:

Table 1

Code: Select all

search_id | term_id | date_created | term type | user_id | group_term_id

1         | 1       | 12/12/07     | keyword   | 24      | unique key
2         | 2       | 12/12/07     | colour    | 24      | unique key
3         | 3       | 12/12/07     | theme     | 24      | unique key
4         | 4       | 12/12/07     | style     | 24      | unique key
Table 2

Code: Select all

term_id | term

1       | grey hound dog in a chair
2       | green
3       | portraits
4       | dada
where group_term_id is a method for grouping all of the stored search data for a particular search (i.e. reconstruct the search at a later date).


I want to do 2 things with this data.
1) Store users searches so they can refer to them later.
2) Get some useful data on popular searches over time, e.g. blue was the most searched for colour in july etc

Any thoughts very welcome! :)

Posted: Thu Mar 08, 2007 9:19 pm
by Christopher
That looks fine. I think I would just use one table to start with. Have a solumn for each search terms. It almost sounds like logging, perhaps your web stats program could help you.

Posted: Tue Mar 13, 2007 4:01 pm
by ed209
thanks for the reply :)

I'm sure there was a reason why I did two tables, I just can't remember it! I don't suppose it will save me much in terms of storage space if I start recording 10,000 + searches?

I'll take a look at my web stats package AW Stats, but I think that might be too advanced for me.