Reputation:
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
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