I'm not a database guru, but I'm planning a database and I'm not sure which solution would be better for the long run ( in terms of query speed ):
A single table that has multiple fields of a set quantity. For example one field might be for a widget description, and then multiple fields for a handful of colors, which would be flagged as either true or false for that particular widget.
** OR **
Two tables, one containing the widget description, and a second table that will use a foreign key to match the widgets with its available colors.
I know normally that having two tables is the best solution, but there will only be about 8 to 10 options ( colors, to follow along with the widget example ) on the second table, which would never increase.
I'm worried that doing a table join when there's about half a million records in the widget table will slow down the query.
Anyone a database expert, or have any experience with this type of situation? Any advice much appreciated.