Storing user searches
Posted: Wed Mar 07, 2007 3:46 pm
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
Table 2
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!
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 keyCode: Select all
term_id | term
1 | grey hound dog in a chair
2 | green
3 | portraits
4 | dadaI 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!