Handling a table with a lot of fields

I have a table to track a production there's so much details to store in the table like 50 different pieces of info.

So my question is should I keep it in one big table or its better to split it up some how.

I use EF 4.0 and connecting to MsSql. the table is so huge that I need to scroll a lot in the entity designer to see all fields...

I don't think of a specific problem having so muc开发者_如何转开发h fields in one table. but if there is some issue with it I want to design it the right way now. not when its to hard to change stuff.

---

Too many fields in a table can results in large tuple sizes, which can make a table scan take longer. A table scan will occur if you are reading data from your table that isn't indexed or can't leverage an index or represents a sufficiently large proportion of your data that an index doesn't help. The other issue is updates. An update will often relocate a row from it's current disk location to a new location. If your update increases the tuple size, and your updates affect only a few columns typically, you're more likely to get table fragmentation. This will slow down access to the data on all fronts. In this case, it can be beneficial to split the table up.

In general, with a good system, unless your table has a good number of rows (probably >50,000), you have appropriate indexes, and you aren't doing excessive querying, there shouldn't be a major problem. If slow queries rear their head, check them against their explain plan to determine if the large table is the problem.

If you do need to split it out, and sometimes that is sensible, then it is possible to create a one-to-one relationship. With SQL Server, I've had issues trying to create a bi-directional foreign key. The only way I've solved this is to ensure that my app doesn't ever delete from secondary tables directly, or create a trigger procedure.

I've not used EF, but with JPA this can be a particularly nasty problem.

---

If the fields are all relevant, directly related to the primary item, and will always only ever have a 1-to-1 relation with that item, then that's fine. Some entities have a lot of details about them, and the right normalization may have all of them in the same table.

---

You should consider normalizing the data amongst multiple tables. This is especially a benefit if there is repeating data in your large table, as well as a possible one-to-many relationship.

Database Normalization is a very large topic within itself, and it goes a mile deep. But there are plenty of resources out there to get a pretty good working understanding of it.