Database design - two tables or one?

[gil857]

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.

Thanks,
Gil

[Stuugie]

If it were me I would create 2 tables and go with your second option. Normalization is important with regards to relational db design. Also, I really dont see how speed will be an issue with your example regarding 2 tables, I wouldnt be worried about that!

My 2 pennies!

[gil857]

Thanks Stuugie