How strict should I be with creating a separate association table?

Question

Let's say I have a table that has a report_type column and I have only 2 report types. Is it worth it to create a report_types table with them, and then reference it with a foreign key to this table? Or is it acceptable to store the report type as a string in that column?

Answer 1

It is if you ever plan on expanding down the road. I think the real question is, is it not worth making the extra table?

Answer 2

Whenever possible you should always plan to normalise your databases and abstract your code. However the more you do this the more "indirection" you'll get in your system. The better question is how much overhead will you incur in normalising the table in terms of effort vs the cost of re-engineering the code further down the road.

In this instance I'd almost certainly say that it IS worth your time to normalise the database.

Answer 3

I would normally suggest normalizing your data but this seems like a simple enough case that you probably won't see much benefit from doing so. As you said, you are only using the field for filtering therefore will only need to use the values present in the data itself.

If you wanted the user to see that there are zero records for a particular report type then you would want to use the associated table.

Answer 4

Things to consider:

• are you likely to add new Report Types in the future?
• are you likely to have other tables which also reference Report Types (e.g. for stats gathering or auditing)?
• are you likely to use these values in other contexts (such as GUI drop-down widgets)?
• do you need to associate additional data (e.g. Long Description) with a Report Type?

I know it seems a bit like overkill to build a table now, to hold just two rows. And quite frankly the gods of Yagni will support you if you decide to stick with a column and a check constraint. But if you already have a bunch of code lookup tables to enforce restricted sets of values, well, there's value in consistency too.

Answer 5

If all reports have the same column set, but you use the report type just to speed-up filtering, then you can simply use:

If there are some extra columns associated with the ReportType -- like ReportTypeDescription -- then use:

If each report type has a different column set -- for example all reports have some common columns (Col1, Col2, Col3) and then each type has few specific columns (Type1_Col1, Type1_Col2) (Type2_Col1, Type2_Col2) -- then use: