First, let me address your concern about data redundancy. I agree with you that the second schema is more likely to reduce the redundancy, and is probably closer to what I would go with. One thing to be aware of, though, is this will be unique search terms. So unless you have some way of normalizing data, misspellings will get through and be viewed as different search terms.
Also, I would switch the way you link the searchTerm to the date searched, so that dateofSearchTerm links to the search term (not the other way around).
CREATE TABLE SearchTerm (
ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
SearchTerm VARCHAR( 255 ) NOT NULL,
Hit INTEGER NOT NULL,
);
CREATE TABLE DateofSearchTerm (
DateID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
searchID INTEGER NOT NULL,
Date DATE NOT NULL
);
It would also be a good idea to make searchID a foreign key that references searchTerm.ID
to ensure there are no orphan rows (rows in DateofSearchTerm
that do not have a matching parent in SearchTerm
).
As for your anecdotal story about your friend, the biggest thing I see you missing in your designs are indexes. A table 3-million strong should have no problem running quickly with proper indexes.
Using your design, you would want an index on the searchTerm.searchTerm
column, and on DateofSearchTerm.date
.
I think if you follow the advice to make searchID
a foreign key, that will also become a proper index. I don't use SQL Server, so I don't know if foreign keys require an index, so make sure that it does.
I'd say it depends on how many parameters you have, and whether this list is expected to be largely static or if it's likely to change or expand.
I'm still a little fuzzy on exactly what you're doing, so I'm going to answer a slightly different question. Let's say you're writing a query engine, searching for users that meet different requirements, and you want to store a set of predicates so it can be reused. In a simple world, your table might look like this:
CREATE TABLE Searches
(
ID INT NOT NULL PRIMARY KEY,
CreatedBy INT NOT NULL, -- Prob also CreatedDate, LastModified, etc.
FirstName VARCHAR(100),
LastName VARCHAR(100),
Sex CHAR(1),
EMailAddress VARCHAR(100),
Country INT
)
Some day you'll need to add a Region
field when you start storing more precise geographic data. Then you'll add PhoneNumber
, and then BloodType
, and then... eventually, you'll need something more flexible. You need a table for searches and a table to store key-value pairs for each search:
CREATE TABLE Searches
(
ID INT NOT NULL PRIMARY KEY,
CreatedBy INT NOT NULL
)
CREATE TABLE SearchTerms
(
ID INT NOT NULL PRIMARY KEY,
SearchID INT NOT NULL FOREIGN KEY REFERENCES Searches (ID),
FieldName VARCHAR(100) NOT NULL,
Value VARCHAR(100) NOT NULL,
UNIQUE (SearchID, FieldName)
)
The UNIQUE
constraint may not be appropriate. For example, it may be appropriate to have a search with Terms { FieldName = "FirstName", Value = "Bo%" } and { FieldName = "FirstName", Value = "ob%" }.
This more flexible approach loses type checking; searching for "NumArms = 2" now requires storing "2" in a VARCHAR
field. It also implies using dynamic SQL to construct your searches.
Best Answer
You need to consider one more parameter, if you are doing it for multispeciality hospital, you should add a
Doctor table,
Department table, also
Doctor schedule (ex: Mon-Wed 10 am to 2pm, Thus-Sat 12 pm to 4pm )