[RESOLVED] is this good table strcture?

lucilue2003

Hey,

i have this table:

CREATE TABLE Listing (
id int(11) NOT NULL auto_increment,
tblAccount_id tinyint,
keyword varchar(50) NOT NULL default '',
title varchar(250) NOT NULL default '',
description mediumtext,
isAdultListing tinyint(1),

url varchar(250) NOT NULL default '',

weight decimal(10,2) NOT NULL default '0.00',
isActive tinyint(1),
isApproved tinyint(1),
PRIMARY KEY (id, tblAccount_id)

) ;

Each member can have one or more listing.
Each listing can have one or more keywords for that listing.
Each keyword has a weight associated with it. The higher the weight, the higher it will appear in the results.

I am working on a PPC engine.

Would it make more sense to have another table for keyword or is it fine the way it is now ?

I assumed that by having the keywords and the weight in one table, i will not need to run a join. Only one select statement that goes through the table and looks at the keyword, title, and description field for any occurance of the search term.

What do you guys thinks ?

Sxooter

The basic rule is, if you can have more than one, you should probably make a separate table for it.

The problem is that if you have too many tables, joins will be slow.

I'd just make the keywords field a text type (no real limit) and put the keywords in as a | delimited set of words or something. It's not like you're likely to do a lot of work on them where having them set up in their own table is gonna help you out.

Roger_Ramjet

Definitely you need a seperate table for the keywords and their weighting, if I understand you. (It always helps to post some example data). Or are you going to be adding up the weighting for all the keywords of each listing and storing that? But then what happens when I search on only one of those keywords? The results are going to be ordered by weighting for keywords that have nothing to do with my search.

Don't be afraid of joins, they are an essential tool when working with relational databases so you had better learn to use them properly. Used properly they can even speed up a query by reducing the number of records that need to be matched against your WHERE conditions. Yours is a case in point, with your current structure you are going to end up doing a full table scan on every keyword search. With the keywords and their weighting held in a seperate table, and linked to the listings by a third table, your queries would just need to join indexes to find the required records.

PS a full table scan is the slowest thing you can do in a relational database: every record read into memory from disk and then compared to your search terms.

An alternative to your approach, and a good one here since you are searching titles and descriptions as well as keywords is Full Text Indexes and Searches. Look them up in the manual.

lucilue2003

I will look up the full text searches and indexes ..
You are right . I will use my weight to figure out which one to display first.

Every listing will have one or more keywords.

For Example:
if you go to google and type in the keyword hosting
http://www.google.com/search?hl=en&q=hosting

you will get this paid listing:

Title - > Windows Hosting for $3.95
Description -> HomepageUniverse.com 1GB Space, 20G Traffic, SiteBuilder ASP.NET, PHP, CGI, MySQL, MsSQL

so this listing was found by doing a search for the keyword: Hosting.
Similiarily, the advertiser might want to place the same listing under the keyword Windows Hosting or host.

I'm just worried that the run time join that im' doing in memory will be too costly....

leatherback

I do not think that you will pay too much for a few joins mor or less. Unless your site is going to attract 10000-s of visitors a day, and you need 10-s of joins in your queries.

For easier querying and weighting, I would use seperate tables. (I personally do this for any field which can hold more then 1 value for a given record.)