How Did You Structure Your Index Mysql Tbl ?

[sunny_pro]

Php Buddies,


I'm going to jump into building my own searchengine now.
Starting off with the search box, then the index and then finally the crawler.
Not too worried about the crawler. Gonna make use of cURL and implode,explode, etc. php functions.
It's the Index that I quite can't get my head around.

To build the Index, should I have the structure of the mysql tbl like this .... ?

Option 1

Columns
Url|Keywords

Or, should I make the structure like this instead ..... ?

Option 2

Columns
Keyword|Urls


Option 1a Example

Columns
Url | Keywords
-----------------------------------------------------------------------
devshed.com | forum, programming, php

Option 1b Example

Columns
Keyword | Urls
--------------------------------------------------------------------------------------------------------------------------
forum | devshed.com, blackhat.com, warriorforum.com
---------------------------------------------------------------------------------------------------------------------------
php | devshed.com/forum/php.htm, sitepoint.com/forum/php.php



Option 2a Example

Columns
Urls | Keyword
-----------------------------------------------------------------------
devshed.com | forum
-----------------------------------------------------------------------
devshed.com | programming
-----------------------------------------------------------------------
warriorforum.com | money
-----------------------------------------------------------------------
warriorforum.com | forum



Option 2b Example

Columns
Keywords | Urls
--------------------------------------------------------------------------------------------------------------------------
forum | devshed.com/forum
--------------------------------------------------------------------------------------------------------------------------
forum | blackhat.com/forum
---------------------------------------------------------------------------------------------------------------------------
php | devshed.com/forum/php.htm
---------------------------------------------------------------------------------------------------------------------------
php | sitepoint.com/forum/php.php


Question 1:
I have a feeling you won't like Option 1a or 1b atall.
But, let's assume you need to do it out of them 2 options. Which one would you choose ?

Question 2:
I have a feeling you will like Option 2a or 2b.
Which one would you choose ?
Or, if you don't like any of them 2. Then, let's assume you need to do it out of them 2 options. Which one would you choose ?

Question 3:
If you don't like the structure of any of the 4 options then which structure would you yourself use or have used ?
Best to show an example like I did.


Btw, I know that, if I structure my tbl around the way I showed in my examples then users would only be able to make queires for a single keyword and not a phrase. But, dealing with phrases get complicated and so for the time being, as a beginner, let's concentrate one thing at a time. Concentrate on the very first basic of indexing a url.

Thanks

 

[Marc van Leeuwen]

I'm going to jump into building my own searchengine now.
Starting off with the search box, then the index and then finally the crawler.
Not too worried about the crawler. Gonna make use of cURL and implode,explode, etc. php functions.
It's the Index that I quite can't get my head around.

I am curious why you don't worry about crawlers because they will decide what the user will see after search on your search engine. You should care what you will get (data) from these sites and when an user searches on your site then it will return the result depends on their queries.

I didn't understand why you would build tables with those structures. More details can help someone here give you better support.

 

[sunny_pro]

sunny_pro

I am going to create the crawler with cURL. The crawler would fetch the pages and then it would grab the keywords from the pages and then dump the keywords and the page urls to my mysql datababase (db). That db is the Index. I'm not too worried about how to build the web crawler because I know how to build it and if I get stuck then this forum members are welcome to help. As for now, I'm building the Index with the mysql db and so need to learn how to structure the mysql table (tbl). Because, if I get the structure wrong then the searchengine would become slow as the search feature would need to do more work to get the results from the Index (tbl).
It is like this, if you dump all your clothes (shirts, trousers, coats) into one drawer in your ward drobe then when you need to rush to a party and need to grab your coat, you're gonna have to wade through all those clothes to fetch your coat. Your fetching would become slow due too disorganisation.
But, if you put your coats in one drawer and your trousers in another and your shirts in another then when you need to grab your coat you won't be looking into the other 2 drawers but the drawer where you usually put your coats. Your fetching would be easier and faster. The fetching was faster in the 2nd example due to you neatly organising the drawers.
My mysql db is like your room (Index).
My mysql db tbl is like your wardrobe (a part of the Index).
My mysql table columns are like your drawers.
My search feature is like you or your fetching hands.
You don't want to be roaming your hands on all 3 your drawers but the appropriate one. Neither do I want to get my search feature to be looking into the wrong tables or the wrong columns but the appropriate ones.
Therefore, need to get the tbl organised with the appropriate columns just like you got your wardrobe organised by discriminating it's drawers.

In short, the tbl structure would narrow the work down if it is structured correctly.