user3064406
user3064406

Reputation:

Searching/indexing by string or by integer

Okay, this is probably a newbie question but I can't figure how best to word it in searching so I'm just asking. Which option below is more efficient and why. The column that changes is a column that will be used a lot as a searching parameter and I'll be indexing it.

Option A, use categoryName in Transactions table:

Table Name: Categories
+--------------+
| categoryName |
+--------------+
| Gas          |
+--------------+
| Coffee       |
+--------------+
| Restaurants  |
+--------------+

Table Name: Transactions
+--------+--------------+-------------+---------+----------+--------+
| userID | categoryName | description | date    | budgeted | actual |
+--------+--------------+-------------+---------+----------+--------+
| 0      | Gas          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 0      | Coffee       | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 0      | Restaurant   | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 0      | Restaurant   | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 0      | Restaurant   | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 1      | Gas          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 1      | Coffee       | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 1      | Restaurant   | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 2      | Coffee       | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 2      | Coffee       | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+

Option B, assign a categoryID to every categoryName and use categoryID in Transactions table

Table Name: Categories
+-----------+--------------+
|categoryID | categoryName |
+-----------+--------------+
| 1         | Gas          |
+-----------+--------------+
| 2         | Coffee       |
+-----------+--------------+
| 3         | Restaurants  |
+-----------+--------------+

Table Name: Transactions
+--------+------------+-------------+---------+----------+--------+
| userID | categoryID | description | date    | budgeted | actual |
+--------+------------+-------------+---------+----------+--------+
| 0      | 1          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 0      | 2          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 0      | 3          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 0      | 3          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 0      | 3          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 1      | 1          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 1      | 2          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 1      | 3          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 2      | 2          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 2      | 2          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+

Upvotes: 0

Views: 29

Answers (1)

Explosion Pills
Explosion Pills

Reputation: 191819

Option B is the answer. Imagine what would happen if you had to change one of the values. Gas becomes Fuel. In Option A, the other table would have a bunch of columns with Gas that would be meaningless.

It is also more efficient to have a key on an integer than a varchar.

Upvotes: 1

Related Questions