I'm facing a decision on how to store URL (bookmark) in a table. I have following requirements:
Customer must be able to search the title of a bookmark.
I decided to use a FTS with ranking option for this requirement
Customer must be able to search an address
Here is where I have doubts on how to store URL. First of all, URLs must be unique in my table. This will potentially be a very large table. There will be no updates or deletes on this table. What I had in mind is this
ID [int] IDENTITY(1,1) -- identity and foreign key in other table(s)
Title [nvarchar](500) -- title (FTS)
CompleteURL [nvarchar](300), -- Unique index example: http://www.bbc.co.uk
URLPrefix [nvarchar](20), -- example: http://www
URLSufix [nvarchar](280), -- example: bbc.co.uk/something (index)
I realize I have data duplication but I have no problem with that since data integrity is not at stake here (no updates or deletes) and performance is important. I was thinking to use CompleteURL as my IF EXISTS column during insert. I'm not sure if this approach will provide better performance than composite key on URLPrefix and URLSufix and to completely lose CompleteURL?
URLSufix would provide quick find for search parameter LIKE 'bbc%'.
But what if customer does search LIKE 'www.bbc%' ? Any wildcards on beginning of a string (%bbc%) are out of the question since I cannot afford scan of entire table.
Furthermore, i decided to move URLs longer than (300) in another table since there more than 99.9% of URLs are not that long and I don't want to hit the index limit of 900 bytes. Is this a good practice?
EDIT:
To make a long story short. How would you store this bookmark
BBC Homepage title
http://www.bbc.co.uk/
if you want it to be returned when search parameter is one of 3 cases:
1. 'homepage'
2. 'bbc.co'
3. 'www.bbc.co'