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!