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" <no****@muelltonne.de> wrote in message
news:ck*************@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
