I have a table 'things' with an auto_increment primary key 'id' and a bunch of other fields. One of the other fields is called 'color'. Is there a way to set up a second table with primary key 'color' that is automatically populated by all of the distinct color values from 'things' (i.e. one row for each type of color in 'things'). I'm pretty new to mySQL. This functionality doesn't seem that complicated but I haven't been able to find anything regarding it. Thanks in advance.
Is there a specific reason that you want a second table with the same data as the primary table? You can use a trigger to do this very easily, but from your initial question it doesn't sound needed to me...
The second table wouldn't just be a copy of the first. It would have other columns that would be changed. For example, I might want to display all of the 'things' sorted by their color, but the sort order of the colors would need to be customized. I guess I could add another column in the first table and have it so that all rows with the same 'color' value have the same 'color_sort_order' value, but it seemed easier to have a second table.