Design question: Storing Keywords. 1

[Brawn]

I have a Product and each product would have an unknown amount of keywords related to the product.

Should I make another table 'Keywords' and associate each keyword to a product Code

Table(field1,field2, etc...)

Products(code,name,description, ...)
Keywords(productCode,keyword)

Or is there a better way,

any suggestions,
Brawn

"My mind is my Shrine,
and my body the Temple around it."
-The difference between genius and stupidity is that genius has its limits-

 

[svanels]

Use the same table, I would include the field Keywords in the products table. I would make it a large field, type memo or something similar and put the related keywords separated by a special sign like # or ;.
Don't know the database you are using, but with the power of stored procedures, retrieving would be easy. S. van Els
SAvanEls@cq-link.sr

 

[shmiller]

Please don't do that.
Create another table. This will make it simpler, faster, and more flexible for everything you do in the future.
Trust me.

 

[sfvb]

I agree with shmiller. Stringing the data together, as SAvanEls suggests, violates the First Normal Form of database normalization.

 

[svanels]

I am looking it from the perspective of the user, unless the designer of the database punches in all the data there will be no problem. But most databases are made for other people with the minimal knowledge of computers.
One thing the designers in most cases don't pay attention to is: who will feed the monster?
The guy behind the screen, if has to punch in a keyword in a separate record for every product, will get bored.
In the end you will have no keyword at all.

Regards Steven van Els
SAvanEls@cq-link.sr

 

[sfvb]

The database shouldn't become a monster. If it is, then either the database and/or the end user interface has some design flaws.

After meeting with the end users, if it is determined that entering all the keywords on one line is best solution for them, then design the interface that way. But don't store the keywords that way. Parse the keywords out of the line before inserting into a seperate Keywords table.

Having a seperate table will speed query results, and make it far easier to update any keywords. For example, try
changing all occurrences the word "cheap" to "inexpensive". It's very easy to do when using a seperate table. However, that is not the case when all the keywords are concatenated.

 

[svanels]

So we come back again to the parsing techniques Steven van Els
SAvanEls@cq-link.sr

 

[sfvb]

And your point would be ... ?

 

[svanels]

I would use the memo type to store a complete description of the product, if I was a seller I would put the keyword cheap in all of my products. Ever tried to describe a video recorder in 5 words?

Regards Steven van Els
SAvanEls@cq-link.sr

 

[Brawn]

I am the end user.
Design and Speed is Key.

Thanks alot, Brawn

- The difference between genius and stupidity is that genius has its limits -

 

[svanels]

Brawn have you ever done a search on this site? The text of the threads is stored in a database, and the query is quite fast. So despite all things of normalizatiom etc, parsing text is a normal thing in todays databases.

Regards Steven van Els
SAvanEls@cq-link.sr

 

[Brawn]

So,

I could have keywords in another table associated with a product, but when searching I could also use the product's description.

This would give me benifits of both methods.

Brawn

- The difference between genius and stupidity is that genius has its limits -