design Q. use one or seperate tables for keywords

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

design Q. use one or seperate tables for keywords

Author

Topic

plocke
Starting Member

15 Posts

Posted - 2001-12-19 : 13:35:58

i have a database with
tables for files and folders.
each file has a pointer to a url and descriptive information.
I also want to add keywords to the files so i can query them by keywords..

lets say the varchar(150) keyword column might include data like:
bithday,party,night - for each file.

is this efficient and logical?

part of me thinks i ought to make a seperate table with individual key words and index and join them to the files..

if i wanted to search for a bunch of files with the keyword: night
i would search the keyword table for every instance of that keyword and join it to the files that the user has access to?

thanks for any suggestions.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2001-12-19 : 13:43:11

Your two options:


SELECT LinkID, LinkURL, Description 
FROM   Links
WHERE   Keywords LIKE '%night%'

SELECT Links.LinkID, Links.LinkURL, Links.Description
FROM   Links INNER JOIN Keywords ON Links.LinkID = Keywords.LinkID
WHERE  Keywords.Element LIKE '%night%'

I would say having them in the same table would be easiest but having each keywork in another table would make less redundant data .. something like


Links
  LinkID
  LinkURL
  Description
 
Keywords
  KeywordID
  ElementName

LinkKeywords
  LinkID
  KeywordID

That would be a pretty spiffy way to address it ... i personally just store it in a column in the table but depends on what you really need to do with this data ... i just need to select items where this name is anywhere in the keyword column
.....

- Onamuji

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-19 : 13:44:22

That's a tricky question. However, if you use Full-Text Indexing, you'll be able to get pretty good performance if you keep them in one column as comma-separated words. Just remember to keep the full-text indexes up-to-date.

You can also use Merkin's technique for CSV keyword searching:

http://www.sqlteam.com/item.asp?ItemID=5857

If you do decide to split them out into a separate table, you've got a good handle on how to use that structure already. It just depends on how much work you want to do to maintain it.

plocke
Starting Member

15 Posts

Posted - 2001-12-19 : 15:05:43

Thanks. Very interesting.
I will have to post up my final solution when finished.
you guys are always so helpful.

santa won't bring you coal for sure.

~ Patrick

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-19 : 17:12:09

Hi

I have been up against that situation. We had keywords in one field, comma separated in something we did. It was fine for ages then they wanted to get more and more detail out of it.

Eventually, they wanted to be able to show every unique keyword across the whole site. I thought about splitting it out to a new table, but decided against it (it would have meant changing a whole bunch of stuff), but I had to resort to using the technique that starts with a C

Anyway, for anything other than that, single field works fine. Look at full text, or the keyword search article works really well too, thats what I ended up doing.

Damian

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-19 : 18:50:46

I like Onamuji suggestions...

Except perhaps you could do away with the artifical key KeywordID on the basis that every Keyword is unique...you can have "cascading" then...

DavidM

"Why are you crying?"

"Because I love a Microsoft product"

GreatInca
Posting Yak Master

102 Posts

Posted - 2001-12-20 : 17:57:13

1 keyword per row would be much more flexible.

FK_FileID, Keyword

GreatInca
Posting Yak Master

102 Posts

Posted - 2001-12-20 : 17:59:34

Full Text indexing would be even better though I've never used search service wher the actual text was not in sql server.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2001-12-20 : 19:58:16

quote:

but I had to resort to using the technique that starts with a C

colonic ?
cornucopia ?
Colgate ?
cacaphony ?

Justin

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-20 : 20:02:41

cunning what ?

Damian

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-20 : 20:06:00

cohort?

-------------------
It's a SQL thing...

need_some_help
Starting Member

5 Posts

Posted - 2001-12-21 : 05:15:41

I'd go with the full-text indexing system myself. That way you could match words that are close to other words: you search for "bakers", and it matches your keyword of "baker". A LIKE statement would not do this. So it's much better.

There are a lot of options when performing a full-tex catalogue search, but SQL BOL explains them all, and it's quite easy to manipulte a search term into a proper SQL catalogue query.

Well worth investigating with. Much to learn :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources