Please consider this setting for a database-enabled application. (in my case, DB is MySQL and the application is in Ruby (Rails 3), but I don't think this is important for this question)
Say I have a warehouse application.
I have several elements that will have categories and statuses.
For example, a table in which there are parts will have several statuses, such as: in stock, discontinued, backward, and several categories, such as: hardware, automobile, medical, etc.
I also have other tables that need statuses and categories, such as Supplier: approved, out of business, new Order: Open, process, ship, cancel.
Etc.
Here is the question:
I think if I wanted to normalize my db correctly - I would have a table called categories, category_types, statuses, statuses_types.
Then I would save all categories in this table, and any category of a certain type, such as all categories of parts, would have a foreign key for category_type - parts, etc. The same goes for types.
This is a normalized way.
However, I often see that people create separate tables for certain categories, for example, there will be a table called part_categories, vendor_categories, order_statuses, part_status. This is a less normalized db, but I think that when you are dealing with a large number of tables, this may be more clear.
Which of these approaches is better? What are the pros and cons in your experience? Usually I go with the first setup, but I see the second so often that I begin to doubt my approach.
.