CODEWITHSUNDEEP
pythondatabase-designsqlite
Jasper
Jasper

Reputation: 2231

Database schema - unable to link the tables in a way that suits the program

I have created a python script to scrape document for keywords. It runs on a server and a cronjob makes sure that searches are performed multiple times per day (with different keywords at different hours).

To store the results I created the following table:

TABLE: 'SEARCHES:'
    search_date(string)
    number_of_results(integer)
    keywords_used(string)     

     ^-- I created a single string from all keywords

All of this was easy to implement in Python/SQLite. However, now I want to track the number of results per individual keyword.

I have already created created a 'keyword' table, 

TABLE: 'KEYWORDS:'
    word(string)
    total_hits(integer)
    last_used(string)

However, I am having trouble coming up with a way of linking both databases in a way that allows me to link the keywords to searches. Presumably the 'searches' table will have a foreignkey linking to the keywords. But there can be as many as 10 keywords per search, yet there is only one foreignkey column.

I looked into ManyToMany relations, but as I understand this would create a large number of rows containing both 'search_id' and 'keyword_id'. Yet, all I need is 1 row per search.

When the program is finished I want create a GUI frontend and be able to list all searches that have been performed in a list / table. Showing not just the keywords that have been used, but information like the search date as well. 1 line per search.

I also want to create a separate overview for the individual keywords, showing their effectiveness.

I'm just unable to come up with a database schema to accommodate this and could use some help to get my nose in the right direction.

Upvotes: 0

Views: 33

Answers (1)

Brian
Brian

Reputation: 1248

I would suggest creating at "Matches" table that is a child to "Searches". Add a "Search ID" field to the Searches table to support the foreign key. The "Matches" table would hold the Search ID, each individual keyword, and perhaps the total hits for that keyword.

Then you can match "Matches" to "Keywords", and go from "Matches" to "Searches" using Search ID.

Upvotes: 1

Related Questions