SQL What you think is better? All on one place or Separate

Which one you think is better? And why?

I always need all data, it will never happen that i need only name and not the "tool1,tool2"...

1) Lets say i have table "Woods" and "WoodsData"

Inside "Woods" i have:

• id (auto increment)
• name (name of wood)
• when (when was woo开发者_如何学运维d cutted down)

And other table "WoodsData"

• id (wich is linked to woods id)
• tool1 (tool no. 1 used to cutdown the tree, true or false)
• tool2
• tool3
• tool4
• tool5
• times (times used the tool)

so the select would be here by joining two tables!

2) One table "Woods" wich looks like this:

• id (auto increment)
• name (name of wood)
• when (when was wood cutted down)
• tool1 (tool no. 1 used to cutdown the tree, true or false)
• tool2
• tool3
• tool4
• tool5
• times (times used the tool)

NOTE: The "tool1,tool2,tool3,tool4,tool5" should go to 50 of them i just didnt wanted to write them all here :P

I'd go with:

Woods

• id (auto increment)
• name (name of wood)
• when (when was wood cutted down)

Tools

• id (auto increment)
• name (name of tool)

And then a junction table to show which tools were used on which woods

Woods_Tools

• wood_id (Foreign key to Woods.id)
• tool_id (Foreign key to Tools.id)

Both are wrong, you should normalize the data: "tool1" to "tool50" should be "tool" in an extra table. Read something about normalisation.

My advice is to put everything in one table. That way you will only have one set of data to insert when an entry is made.

If you have two tables you will need to update two tables (clustered indexes if they are sql server tables with primary keys). That can take extra effort.

As long as you really need all the data (tools1..50) every time you select from the table then I think it is better to use one table.

The best solution is to try it both ways and measure the results. It might not make a difference either way.

To be more normalized, separate the 'tool' entries into a third table

WoodsTool:

• id (primary)
• woodsdata_id (foreign key, relates to WoodsData primary key)