Hi,

I'm trying to improve the design and performance of this database.

This is the database for a web-site.
The search will be mostly done on the product description and there can be up to 2 mil search requests / month

My first design comments would be to :
1) remove the Cla_Id field from the List_Prod table
2) remove the Manager_Cla_Id from the Manager table
because the List_Id - Cla_Id relationship is already in the List table, and it does not have to be duplicated in other tables. I think this comes from a normal form, but I'm not sure though.

Can you please help me to understand if my initial comments are good and what can I do more to improve the performance of the searches.

I thought of indexing, but it sounds quite strange for me to index a character field (Prod_Desc).

Thanks,
Ronnyy

NOTHING wrong with indexing a character field. Why on earth would you think so? However - how will you be searching it? Will you be searching for exact matches or "contains this phrase"? Percentage of table likely to be returned (range)?

pootle flump ur codings are working excelent.

I was thinking that if you offer such a functionality to search on a product description, which is a VARCHAR2(60) type, then you can't really asume that a user will input an exact match of that Product Description even if you request them, because in the end it's user's input

Therefore I was thinking how much does it help to build an index on that table, what's the performance benefit of building such an index assuming that there will be both exact matches and partial matches.


In terms of the data to be retrieved from the search I would say that a join between the List, Clasification and PRoduct table is necessary, and I wonder if the other two tables cannot be included as well, as there are simple joins with the Manager and Persons tables.

I just realised that the List table has not the best design, it is a table that holds a tree, and maybe a nested set design would be better.

Ronnyy

No point indexing it then.

Is this Oracle? I can't speak for the internal mechanisms of that. You are really mixing logical and physical design ATM. Probably best to stick to the logical for now and sort out physical in the Oracle forum.

pootle flump ur codings are working excelent.

if you are worried about invalid user inoput then you could consider developing some form of second guseesing algolrhtym.. similar to the soundex or double metaphone. Whether thats worth the effort I don't know. It would be a farily significnat taks, it would require you to break up row into words, or use a key words column

I'm also surpised you don't have other prod details (name, manufacturer, item code) etc..
Another alternative (but requires some sort of input - manual or otherwise) is a one to many relationship to a products_tags table containing features & other common search terms. This you could index and provide a drop down for people to search by rather than allowing free text entry.

pootle flump ur codings are working excelent.

I'd leave this problem for the database engine to compensate... Nearly all engines today have some flavor of "full text search" that trades disk for the ability to cope with ill-formed text searches (mis-spelled words, words out of sequence, etc). I can't imagine adapting my schema to do a job that the engine can do better than I can.

-PatP

Thank you for your answers

I forgot to mention that this is not my design, as I said I would do it with a different type of tree in the List table, so sorry if I can't provide you information on why they did it like this.

Pat P, making the problem more general, if you have a web-site with 5 mil products lets say(I know that in practice 5 mil may be too much) than you just do a
SELECT COLUMNS FROM TABLE WHERE SEARCHED_COLUMN LIKE '%SEARCHTERMS%'; ?

I thought there should be something better than this.

The Full Text search that you are talking about doesn't it search on all the columns in the database? isn't it better to search only on one column if you know that there is only one column to search on?

I'm just asking because I don't know it, please understand that I might be wrong.

Ronnyy

We need to see some sample data and some sample search terms.

Product Description can be:
Mountinebike 7 speeds,
BMX Bike Midle size
Godzilla T-Shirt
...

List can be T-shirts, Bikes, etc.

Clasification can be In-door, Out-door, etc...

Unfortunatelly I don't have access to the database, I made a request and now I have to wait.

The design that I have to improve is on paper, and now when I'm reading the design for the 30th time, I realize that maybe I miss interpreted it, because on paper the List table for example has the List_ID and List_CLA_ID fields underlined, but the other fields are not underlined like this
Now I'm thinking that maybe the List_ID and the List_CLA_ID make up the PRIMARY KEY of the List table and this changes a lot the design.

This means that a List can be classified in more than one classification.

Another important aspect is that the web-database is a copy of another master database, so we can assume there will be no inserts and updates on the web database with the exception of the normal daily copy from the master database to the web-database

Just playing devil's advocate.

I'm not sure that a one to many list of features\ tags is trying to do a home brew version of full text search.

1) Can you be\ are you sure that the descriptions are comprehensive with regard to all, or the main, search terms a customer is likely to use to find a specific item?
2) Would you like customers to have a drop down list of search terms, perhaps in a hierarchy, or is a little google text box sufficient?
3) Common features allow you to loosely group items. "If you liked this you might like...".

One to many feature tables are not that rare. For really structured, feature driven stuff it is very common e.g.:
http://www.laptopsdirect.co.uk/AdvSearch/
People are also getting more and more used to utilising tags from web 2.0 sites.

pootle flump ur codings are working excelent.