1

Is it okay to have a one column table for categories for my homemade blog? I really cannot see what else should be in this table.

This table is going to have a many to many relationship with posts table.

My reasoning is that I don't want identical categories and in that way my categories would also be the primary key.

TT.
  • 15,774
  • 6
  • 47
  • 88
Morten Due Christiansen
  • 277
  • 1
  • 3
  • 15
  • maybe you can have CategoryID (**INT**) _AI_ _PK_, CategoryName (**varchar**) _not null_ so that you can refer to the category from the posts using just the CategoryID.. using an inner or left join – Jones Joseph Mar 13 '16 at 06:59
  • 2
    i thought of that. but my thoughts on that is that im only adding an id for the sake of adding it. CategoryName is a perfect candidate key because its unique. and there for it could be a primary key – Morten Due Christiansen Mar 13 '16 at 07:03
  • 2
    having an ID will be efficient and flexible in case you want to improvise.. for eg suddenly after a month you feel the need for adding parent aand child categories.. so just adding one column to the table would do the job..... so the complete table would look like.. CategoryID (**INT**) _AI_ _PK_, CategoryName (**varchar**) _not null_, ParentID (**int**) .. youo can build a tree view with this.. when dealing with database, having an ID field separately always helps @morten – Jones Joseph Mar 13 '16 at 07:10
  • He can do all the same things without a surrogate key. However, I wonder if Entity Framework might affect the choice, ORMs tend to assume surrogate keys. For more info on choosing keys, see http://stackoverflow.com/questions/3632726/what-are-the-design-criteria-for-primary-keys. – reaanb Mar 13 '16 at 09:54

2 Answers2

2

I would add a surrogate key for 2 main reasons:

  1. Using a surrogate key will enable your joins to work on ints rather then on strings, which is more efficient
  2. Using a surrogate key will enable you to rename your categories without having to go through all of the rows related to that category and change them as well. I know that most modern databases will allow for update-cascade, but it does take more work on the database side even if you don't have to do it manually.

Of course, you must also keep a unique index on the category name, and mark it as non-null, otherwise you might end up with 2 ids for the same category name or an id with a category name that is null.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • edited my answer after reading the comment by a_horse_with_no_name to the other answer. Of course, I should have thought about it myself. – Zohar Peled Mar 13 '16 at 10:56
0

Logically, there's no problem with a single-column table using a name as key, if you want to predefine categories before recording posts. If categories can't exist without posts, you don't even need this single-column table.

Practically, names are less stable than surrogate keys. Unless you expect huge datasets, your DBMS doesn't support ON UPDATE CASCADE, or you design the database poorly, that isn't much of a concern. If your category names are long and you expect huge datasets, record size, index size and join performance might become a concern. On the other hand, using the category name directly negates a join (with the categories table to get the name). Your choice of key scores well against the other criteria specified in What are the design criteria for primary keys?

To conclude, a single-column table is fine for the requirements you specified.

Community
  • 1
  • 1
reaanb
  • 9,806
  • 2
  • 23
  • 37
  • 1
    The total index size will actually be higher with a surrogate key. One index for the surrogate key and another (unique) index on the category name. –  Mar 13 '16 at 10:30
  • For the categories table, yes, but smaller for related tables. – reaanb Mar 13 '16 at 12:57