Mysql – How to design a database for search history with multiple facets

database-designMySQLschema

I want to show users a history of their searches, which can often be complicated:

any of these terms: [a,b,c]
all of these terms: [d,e,f]
not these colors: [h,i,j]

Since these searches are done through a UI (not a search input where they would type "a OR b etc...") I assume I would need to store each value in a table.

Would it be overkill to create a table for each facet ("search_terms", "search_colors", etc)? or is there a simpler way?

| search_id |   term(_id)  | operator(enum)|
|:---------:|:------------:|:-------------:|
| 1         | blue         |     any       |
| 1         | green        |     any       |
| 1         | red          |     any       |
| 1         | musthave     |     all       |
| 1         | exclude_me   |     not       |
| 1         | exclude_also |     not       |

Some other considerations are:

The web-URL can change over time, so I don't want to store that as an easy way out.
The index/3rd party service I'm searching could change, so I similarly can't save the query I make.

(The terms themselves can be its own table with a reference instead).

Best Answer

What you store should mimic what the user sees in the UI. That way you can present it in a format that is familiar to him.

This format may, or may not, be anything close to the SQL that has ANDs and ORs.

Related Solutions

Mysql – Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search

Switch to InnoDB, and

Set innodb_buffer_pool_size to at least 250M
Show us the tentative SELECTs
SHOW CREATE TABLE (DESC is inadequate)

Mysql – Database Design: What is the best structure for storing site offices and locations

Here is some vanilla SQL:

CREATE TABLE RoomTypes
(
 RoomType VARCHAR(12) NOT NULL,
 UNIQUE (RoomType)
);

CREATE TABLE Zones
(
 Zone VARCHAR(10) NOT NULL,
 UNIQUE (Zone)
);

CREATE TABLE Rooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 UNIQUE (RoomType, Zone, Name), 
 FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
 FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);

CREATE TABLE NumberedRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Numbered'),
 UNIQUE (Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

CREATE TABLE AncillaryRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Ancillary'),
 UNIQUE (Zone, Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.

The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.

Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.

Best Answer

Related Solutions

Mysql – Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search

Mysql – Database Design: What is the best structure for storing site offices and locations

Related Question