August 2, 2006 at 3:45 am
HI Folks,
I have been asked by one of the departments to set-up a new database. Queries on this database are going to be inefficient which is why I am here. Basically they are going to have the following fields
first name:
last name :
add1 :
add2 :
add3 :
add4 :
Hanging onto this there will have a front end which will contain 2 fields.
name :
address:
My question is, what is the best way to set-up this database for querying? If someone enters a name I will have to do a search based on the first or second name. If they enter an address I will have to do a search against the four address fields... Should I just use indexes against these four fields?
Begining there will be about 800 rows in the database but this could increase up to about 10000 in a week or two. I want to make it as efficient as possible. Should I make the four address fields composite keys - but these are inefficient aren't they?
Another thing I was thinking of doing was concatenating the fields and searching against that. would that be the best way around it?
If anyone has any feedback greatly appreciated cos they wanted this the day before yesterday and now I find myself under pressure for answers.
THanks
M.
August 2, 2006 at 4:27 am
You need to give each table a way to be linked to the other table, use a new field called say ContactRef, then you can create a view based on linking the 2 tables together to search on. The volume of records is tiny for SQL Server so you shouldn't be too worried about it.
Regards
Carolyn
August 2, 2006 at 7:42 am
M,
Carolyns right, you really need to employ some decent database design to this, i.e. Nomalizing the design, i.e. splitting the data up into manageable tables and reducing duplication.
A good start would be to create the following 2 tables as such (Primary Key and Foreign Key constraints excluded at present, these will need to be added when creating the tables):
CREATE TABLE dbo.Contact (ContactID INT, ContactFirstName VARCHAR(128), ContactLastName VARCHAR(128))
CREATE TABLE dbo.ContactAddress (ContactAddressID INT, ContactID INT, Add1 VARCHAR(128), Add2 VARCHAR(128), Add3 VARCHAR(128), Add4 VARCHAR(128))
You can then return all ContactAddresses for a particular contacts name by doing a JOIN:
DECLARE @SearchName VARCHAR(128)
SELECT ContactFirstName, ContactLastName, Add1, Add2, Add3, Add4 FROM dbo.Contact
INNER JOIN dbo.ContactAddress ON dbo.Contact.ContactID = dbo.ContactAddress.ContactID
WHERE ContactFirstName LIKE '%' + @SearchName + '%' OR ContactLastName LIKE '%' + @SearchName + '%'
This approach would also provide a more scalable design as you can assign multiple Addresses to a contact.
As for Carolyn's comment about the volume of records, you are right to be wary as a badly designed database with poor queries can cause all kinds of locks on server resources even with only 10000 records (which will surely grow with use anyway).
Once you have the basic database in place you won't go far wrong by creating clustered indexes on the primary keys (i.e. dbo.Contact.ContactID and dbo.ContactAddress.ContactAddressID) and non-clustred indexes on the foreign keys (dbo.ContactAddress.ContactID). You can then tune your database with additional non-clustered indexes on some of the search fields if and when required.
Hope this is helps,
ll
August 2, 2006 at 7:54 am
Guys,
Thanks for the prompt replies... They've certainly sent me in the right direction. Upon talking to the developers more, I've found that they're going to need some sort of fuzzy logic algorithms as these users(farmers - no offence to any one that maybe one or who wants to be one...) are not going to know the full names properly.
Bearing this in mind, I think I'm going to have to use full-text querying.
Thanks again,
You've been a great help.
M
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply