Have less tables with a lot of data or a number of tables with smaller amounts of data?

Lets say I have a number of tables that will have a type and status and I want the types/statues to be configurable by a end-user of the application (so I can't use enums here). There are 2 different ways I am think about doing this:

• Have Types and Statuses tables for each table that needs them.

• Have one Types and Statuses table that every table that needs them uses

What are the benefits/disadvantages of each of these methods? Which one would you go with?

Only you can really answer that question. If you are trying to determine whether to have a global Types table and a global Statuses table for all other tables that have this data, or whether to have a Types table and a Statuses table for each table that requires type/status data.

Would the pertinent data fields be the same or similiar? In other words, would the multiple tables have the same schema and structure? If so, you could think of combining them. How many tables are we talking about, and how many Types and Statuses would there be? You don't want to saturate what would normally have been a quick operation (though choose indexes and keep statistics updated often obviously).

Answering these questions will allow you to determine what to do.

But the main point here is that, if the Types and Statuses are not related then they should be kept in separate tables. You never know if down the line you want to add a certain field. That'd be much easier if it's isolated to a smaller Type and Status table.

StackExchange Database Administrators Q#15781, answer score: 3