Say I have a table of schools, and each school is either Public, Private, or For-Profit. As well, each school is either '2-4 Year,' '4+ Year', or 'Graduate'. My initial instinct in this situation is to create another table that holds attributes, then create a junction (many-to-many) table to map a school to its attributes. This seems simple enough, but every time I am faced with this situation my stomach begins to turn and the following two questions always arise:
Is it worth it? What reason really is there not to just store the text (Public, Private, For-Profit) in a column in the school table?
The other issue is in naming. Say the first table is named school, the attribute table is named school_attribute (assume there are also attributes that apply to other things, e.g., companies), then I am stuck naming the junction table school_school_attribute, and that just sounds terrible. The naming problem often leads me to not normalize my table, even though I know that's the wrong thing to do. However, I'd almost rather be stuck with a database that is not 3NF than redundant naming in my tables.
So, first I'd like to know if it is even worth it, and for what reason (e.g., it saves space); second, I'd like to know how people name their tables to avoid redundancy.
Another option would be to create a table called attribute with a column named type. The type column would tell me what type of attribute it is (e.g., school, company, etc). Wait, no reason to store the text in the column, let me create a table called attribute_type, and now a junction table named attribute_attribute_type... Aggg! This is my biggest block in database design for some reason.