search history - schema suggestions?

I need to track all search history of my users and show them back. One row per search item does not seem the best way to store this as it will grow fast and big. What other technologies are there to store this kind of data. This开发者_如何转开发 is write once and read always. Search history will be displayed on many pages.

I would start by separating the business need from the soluton and any potential technical problems (I.E., database performance).

The business need should articulate the problem and what is the expected outcome. For example you may be required to keep all of the search history but only display the latest 50 in a highly visible section of your UI. The remainder of the search history might be dislayed in some form of grouping (I.E., year and month) and only shown within the UI from a separate screen / report.

Understanding this need will then show you the most simple and most likely suitable solution. Remember that you can always vertically or horizontally partition your data if performance is an issue down the track. Using the above example of only keeping the latest 50 searches you could create a multiple table solution with one table containing the latest and current information and other tables for the archived searches. You may then want to store the archived search history in some form of XML data type.

Hope this helps

What's wrong with having it grow fast and big? How big is big? How slow is slow?

Have you tested to see how long it takes to find 100 rows out of a table that contains 100 million rows? How much do you know about indexes? About normalized design? About Star Schema design?