I came across a decision on how to store a URL (bookmark) in a table. I have the following requirements:
The client should be able to search for the bookmark title.
I decided to use FTS with a ranking parameter for this requirement
The client should be able to search for an address
This is where I doubt how to store the URL. First of all, the URLs must be unique in my table. This will potentially be a very large table. There will be no updates or deletes in this table. What i meant is
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 understand that I have data duplication, but I have no problems with this, since data integrity is not set here (no updates or deletes), and performance is important. I was thinking of using CompleteURL as an IF EXISTS column during insertion. I'm not sure if this approach will provide better performance than a composite key on URLPrefix and URLSufix , and completely lose CompleteURL ?
URLSufix will provide a quick search for the search parameter LIKE 'bbc%' .
, LIKE 'www.bbc%'? (% bbc%) , .
, URL , (300) , 99,9% URL- , , 900 .
?
EDIT:
.
BBC Homepage title
http://www.bbc.co.uk/
, , :
1. 'homepage'
2. 'bbc.co'
3. 'www.bbc.co'