How to build a category table

Question

Sorry for the not so great title, but I'm curious how people build a category table in SQL given the following example

Two columns: ID, CATEGORY

Which of these ways would you name the columns?

ID, CATEGORY

ID, DESCRIPTION

CAT_ID, CAT_DESC

Or something else? Just wondering what other people do. Sorry if this is less than clear. I hope it's worth answering because feedback would be great.

Answer 1

I personally don't like the "CAT_ID" "CAT_DESC" nomenclature, I much rather prefer "DESCRIPTION" and "ID".

However, the downside to this is that if you are doing joins on tables which have the same column names, in the projection, you have to rename the columns to indicate which attribute is from which underlying table.

The downside to the "CAT_ID", etc, etc approach is that you can come up with names which don't have a simple singular meaning.

Your usage of the table and the types of queries/joins/projections should help you determine which is best suited for what you are doing.

Answer 2

I am assuming you want a category table with auto-generated IDs and corresponding category names. If that is the case, I would name the table CATEGORY and have the fields ID and NAME.

Answer 3

This is really up to you and what your project requires. You may want to go with:

• id (128)
• title (Dogs and their Owners)
• description (Training information for new dog-owners)
• item_count (6)
• is_active (y)

Depends on your project and its requirements. This can be as simple as you like, or as complicated. Other fields may include:

• date_created (2009-07-10 05:32:28)
• parent_id (For sub-categories)
• created_by (user123)
• ...

Answer 4

I would call them "Category_ID" and "Category".

That way

• If you join it with another table and that table has a column "Category_ID" it has the same name as the "Category_ID" in the Category Table.
• If you join it with another table, the column "Category" is clearly identified, which would not be the case if you had "Description" (and "Description", too, for the items in the category).

Answer 5

This question is very subjective. As with most programming problems, there are a million and 1 right ways to do things. it's all about your personal preference and what you think is going to be the easiest. You need to consider what information about the category is going to necessary, and what other tables you are going to categorize. You will definitely need a primary key column in your table, because i assume you'll want to join the category table on another. but other than that, you might consider a short text field called title or something, and a long text field called description or something like that. If you're going to allow users to create these objects through a webui, you might want to include a user_id column. again, just think about what you actually need this table for, and design it to suit your needs.

-C

Answer 6

I'd call the table "category" and the columns "id", "name" and "description". "category" is a bad name for the name/description column since it doesn't really tell you what it is. Name and description are really two different things, hence the two columns. You can leave the description out if you only want the name.

"id" vs "category_id" is even more subjective, just be consistent.

Answer 7

As others have stated, this is very subjective and where there are no absolute rights (that being said, there certainly might be a wrong way to do it, or at least some unfavourable ones).

Personally I'd create a table called "category" with fields id_cate, name_cate and description_cate (or something alike, but you get the basic idea). The reason why one would concatenate every name with "_cate" is that when you do link tables together in a query where fields have the same name, you will now by its extension what table it belongs to, therefor eliminating the need to do

select * from Table1Name inner join Table2Name on Table1Name.ID = Table2Name.ID

You will just write:

select * from Table1Name inner join Table2Name on id_tab1 = id_tab2

As I said and has been stated by others, there is no best way to do this. Everyone just develops a style / technique that they are most comfortable with and seems most readable. As long as you are consistent and comfortable with your style it doesn't matter that much.

Answer 8

• The table name is category - that itself is telling you something.
• The column names can be simple "ID", and "DESCRIPTION"
• category.id, and category.description look clean and simple