Hi,
I would like to use keywords inside a record to search for - like a
searchengine.
How can I design the table? Right now I would store data in Memo-Column in a
format like ";apple;banana;lemon;". I would use a INSTR(ring)-function - but
I wonder if the performance is still OK then - even when I index the field.
Are there maybe even better ways?

Thanx in advance.
Greg

Each field in a given row should store one piece of information. Seperating
your data with semicolons suggests that your storing multiple piecies of
data in a field that should only be storing a single peice of data. It
defeats the puprose of a relational database. Instead you probably want to
add a second table that will have a one to many or a third table to setup a
many to many relationship with your first table and possibly second. Here
are two table ideas but without your DDL I can't know what it is your trying
to do so I'm guessing. If you need more information provide your DDL (i.e.
your create statements, and some insert statements with sample data).

CREATE TABLE Food_Type
(
ID INT IDENTITY PRIMARY KEY NOT NULL,
Name VARCHAR(50) NOT NULL
)

CREATE TABLE Food_Attributes
(
ID INT IDENTITY PRIMARY KEY NOT NULL,
Attribute VARCHAR(50) NOT NULL,
Food_Type_ID INT NOT NULL
)

Then your data could look like this.

INSERT INTO Food_Type VALUES ('Dinner')
INSERT INTO Food_Type VALUES ('Lunch')
INSERT INTO Food_Type VALUES ('Snack')
INSERT INTO Food_Attributes VALUES ('Chicken',1)
INSERT INTO Food_Attributes VALUES ('Sandwich',2)
INSERT INTO Food_Attributes VALUES ('Orange',3)
INSERT INTO Food_Attributes VALUES ('Bannana',3)
INSERT INTO Food_Attributes VALUES ('Apple',3)


Now you have two tables that look roughly like this:

ID Name
---------------------------------------
1 Dinner
2 Lunch
3 Snack

ID Attribute Food_Type_ID
----------------------------------------
1 Chicken 1
2 Sandwich 2
3 Orange 3
4 Bannana 3
5 Apple 3

Then if you wanted you could select all the food attributes that belong to a
snack

SELECT Food_Attributes.Attribute
FROM Food_Attributes
JOIN Food_Type ON Food_Type.ID = Food_Attributes.Food_Type_ID
WHERE Food_Type.Name = 'Snack'


This will return a result set like this

Attribute
------------------
Orange
Bannana
Apple

Best,
Muhd

"Greg Cyrus" <nospam@muelltonne.de> wrote in message
news:ck1f0a$da2$03$1@news.t-online.com...
> Hi,
> I would like to use keywords inside a record to search for - like a
> searchengine.
> How can I design the table? Right now I would store data in Memo-Column in
> a format like ";apple;banana;lemon;". I would use a INSTR(ring)-function -
> but I wonder if the performance is still OK then - even when I index the
> field. Are there maybe even better ways?
>
> Thanx in advance.
> Greg
>

"Greg Cyrus" <nospam@muelltonne.de> wrote in message
news:ck1f0a$da2$03$1@news.t-online.com...
> Hi,
> I would like to use keywords inside a record to search for - like a
> searchengine.
> How can I design the table? Right now I would store data in Memo-Column in
> a format like ";apple;banana;lemon;". I would use a INSTR(ring)-function -
> but I wonder if the performance is still OK then - even when I index the
> field. Are there maybe even better ways?
>
> Thanx in advance.
> Greg
>

Memo and INSTR() are Access terms, I believe, not MSSQL. Assuming you are
using MSSQL, there are functions like CHARINDEX() and PATINDEX(), but if you
have a lot of text in one column, then you should look also at full text
indexing (see Books Online).

As a side comment, it's not clear from your example what the data is, but
delimited text in a single column is often a sign of an incorrect data
model. You might want to review the model, and see if you can store only one
word in each column, which would make normal indexes much more effective.
But this may not apply, since your real data may look totally different.

Simon

hi simon,

PATINDEX is a very good usage for what I want. I just wonder about the
performance on a SQLSVR.

> [a] delimited text in a single column is often a sign of an incorrect data
> model. You might want to review the model, and see if you can store only
> one word in each column, which would make normal indexes much more
> effective.

that would make the query very oversized when using OR's and AND's: like
where (col1='apple' and col2='banana') or (col1='banana' and col2='apple')
and this is only a 2-words example.

greg

hi Muhd,

thanx for this very good and detailled example (i also make it very detailed
when I answer on quests).
a dictionary-table (Food_Attributes) makes it also very lean and a good
data-model.(->simon).

greg