Most efficient design to search for this data in my database?

Question

Hi folks,

I have the following database tables and a view which represents that data. The tables are heirachial (if that is how u describe it) :-

EDIT: I've replace my 3 tables with FAKE table names/data (for this post) because I'm under NDA to not post anything about out projects, etc. So yeah.. I don't really save people names like this :)

FirstNames

FirstNameId INT PK NOT NULL IDENTITY
Name VARCHAR(100)

MiddleNames

MiddleNameId INT PK NOT NULL IDENTITY
Name VARCHAR(100) NOT NULL
FirstNameId INT FK NOT NULL

Surnames

SurnameId INT PK NOT NULL IDENTITY
Name VARCHAR(100) NOT NULL
FirstNameId INT FK NOT NULL

So, the firstname is the parent table with the other two tables being children.

The view looks like...

PersonNames

FirstNameId
FirstName
MiddleNameId
MiddleName
SurnameId
Surname

Here's some sample data.

FNID FN   MNID    MN     SNID  SN
-----------------------------------
  1  Joe    1   BlahBlah   1  Blogs
  2  Jane   -    -         1  Blogs
  3  Jon    -    -         2  Skeet

Now here's the problem. How can i efficiently search for names on the view? I was going to have a Full Text Search/Catalogue, but I can't put that on a view (or at least I can't get it working using the GUI against a View).

EDIT #2: Here are some sample search queries :-

exec uspSearchForPeople 'joe blogs'  (1 result)
exec uspSearchForPeople 'joe'        (1 result)
exec uspSearchForPeople 'blogs'      (2 results)
exec uspSearchForPeople 'jon skeet'  (1 result)
exec uspSearchForPeople 'skeet'      (1 result)

Should i generate a new table with the full names? how would that look?

please help!

Answer 1

This doesn't seem like the most logical design decision. Why did you design it like this?

What's your indexing structure currently? A index on Name on each of the 3 tables should speed up the query?

Alternatively, normalizing further and creating a Name table and having NameID in each of the three, then indexing the Name table should also increase performance, but I think indexing the name field on the 3 tables would be easier and work as well.

What's the stats on updates vs selects, as adding these indexes might incur a performance hit.

Answer 2

crazy design, possibly the fake table names makes it stranger than it is.

create indexes based on select usage.

if you are searching on actual first names like "Joe" you need an index on FirstNames.Name

if you are searching on first name ids like 123, you have an index: FirstNames.FirstNameId

if you want to search on FirstNames.name and/or MiddleNames.name and/or Surnames.name you need to have indexes on the combinations that you will use, and the more you make, the harder for the query to pick the best one.

ditch the view and write a dedicated query for the purpose:

go after first/middle

select
  FirstNames.name
    ,MiddleNames.name
    ,Surnames.name
  FROM FirstNames
    INNER JOIN MiddleNames ON FirstNames.FirstNameId=MiddleNames.FirstNameId 
    INNER JOIN Surnames ON FirstNames.FirstNameId=Surnames.FirstNameId 
  WHERE FirstNames.Name='John'
    AND MiddleNames.Name='Q'

go after last

select
  FirstNames.name
    ,MiddleNames.name
    ,Surnames.name
  FROM Surnames 
    INNER JOIN FirstNames ON Surnames.FirstNameId =FirstNames.FirstNameId
    INNER JOIN MiddleNames ON FirstNames.FirstNameId=MiddleNames.FirstNameId 
  WHERE Surnames.Name='Public'

just make sure you have indexes to cover your main table in the "where" clause

use SET SHOWPLAN_ALL ON to make sure you are using an index ("scans" are bad "seeks" are good")

EDIT
if possible break the names apart before searching for them:

exec uspSearchForPeople 'joe',null,'blogs'  (1 result)
exec uspSearchForPeople 'joe',null,null     (1 result)
exec uspSearchForPeople  null,null,'blogs'  (2 results)
exec uspSearchForPeople 'jon',null,'skeet'  (1 result)
exec uspSearchForPeople null,null,'skeet'   (1 result)

within the stored procedure, have three queries:

if @GivenFirstName is not null 
    --search from FirstNames where FirstNames.name=@value & join in other tables
else if @GivenMiddleName is not null 
    --search from MiddleNames where MiddleNames.name=@value & join in other tables
else if @GivenLastName is not null 
    --search from Surnames where Surnames.name=@value & join in other tables
else --error no names given

have an index on all three tables for Names.

if you can not split the names apart, I think you are out of luck and you will have to table scan every row in each table.

Just think of a phone book if you don't use the index and you are looking for a name, you will need to read the entire book

Answer 3

I would have just one table with a name type column (first, middle, last) and an FK onto itself with the clustered index on the name column.

   CREATE TABLE [Name] (
        NameID INT NOT NULL IDENTITY,
        [Name] varchar(100) not null,
        NameType varchar(1) not null,
        FirstNameID int null,
    )

    ALTER TABLE [Name] ADD CONSTRAINT PK_Name  PRIMARY KEY NONCLUSTERED (NameID)
    ALTER TABLE [Name] ADD CONSTRAINT FK_Name_FirstNameID FOREIGN KEY (FirstNameID) REFERENCES [Name](NameID)
    CREATE CLUSTERED INDEX IC_Name ON [Name] ([Name], NameType)

    DECLARE @fid int
    INSERT [Name] ([Name], NameType, FirstNameID) VALUES ('Joe', 'F', NULL)
    SELECT @fid = scope_identity()
    INSERT [Name] ([Name], NameType, FirstNameID) VALUES ('BlahBlah', 'M', @fid)
    INSERT [Name] ([Name], NameType, FirstNameID) VALUES ('Blogs', 'L', @fid)

    INSERT [Name] ([Name], NameType, FirstNameID) VALUES ('Jane', 'F', NULL)
    SELECT @fid = scope_identity()
    INSERT [Name] ([Name], NameType, FirstNameID) VALUES ('Blogs', 'L', @fid)

    INSERT [Name] ([Name], NameType, FirstNameID) VALUES ('Jon', 'F', NULL)
    SELECT @fid = scope_identity()
    INSERT [Name] ([Name], NameType, FirstNameID) VALUES ('Skeet', 'L', @fid)

You could then build a dynamic but paramterized WHERE clause based on the number of values to search (or hard-code them for that matter assuming there are only at most 3) using sp_executsql in a stored proc, linq to sql, or even ugly string manipulation in code.

Answer 4

I think what you are wanting is an Index table. It doesn't matter how many tables and columns you have in those tables as stuff is inserted into the database it gets indexed. ex.

I would recommend one table for your names.

NameTable
----------
Id
FirstName
MiddleName
LastName

You can have as many normal tables as you want...

IndexTable
----------
Id
Text 

You could use the text as the primary key but I always have a separate id column for the primary key (just habit).

IndexItemTable
----------
Id
IndexId // Has a foreign key reference to IndexTable Id
ReferenceId // The record Id of where the text occures
ReferenceTable // The table where the  text occures

Then as you insert a name "Jim Barbarovich Fleming" you would also scan you index and find that its empty and create 3 new records for Jim, Barbarovic, and Fleming that would all have the same referenceId and the ReferenceTable would be "NameTable" then you insert another record like "Jim Bradley Fleming" you would scan the index table and see that you already have values for "Jim" and "Fleming" so you would just create IndexItem with referenceId of 2 and ReferenceTable of "NameTable".

By building and index you can search via a single textbox and find all records/fields in your database that have those values.

Note: you going to want to change everything when you insert it to the index to uppercase or lower case and then use equals(value, OrdinalIgnoreCase).

Edit: I can't just upload the image. I have to host it somewhere I guess but It not any different than the table diagrams I put above. The only relationship IndexTable has is to IndexItemTable. I would do the rest in code. ex.

During Insert or Update of new record in Name table you would have to:

1. Scan IndexTable and see if each of the fields in the NameTable exist.

2. If they don't you would add a new record to the Index table with the text that wasn't found. If they do the go on to step 3.

3. Add a record in the IndexItemTable with the referenceId (the id of the record in the NameTable) and ReferenceTable (NameTable) and then the IndexId of the text found in the IndexTable.

Then when they do a search via your single text box you search for each word in the index table and return the Names from the NameTable that are referenced in the IndexTable.