SQL Server: one table with 400 columns or 40 tables with 10 columns?

Question

I am using SQL Server 2005 Express and Visual Studio 2008.

I have a database with a table with 400 columns. Everything was (only about managed), until I had to perform bi-directional synchronization between multiple databases.

I am wondering what are the pros and cons of using a 400 column database or 40 table database?

The table is not standardized and contains mainly nvarchar (64) columns and some TEXT columns. (no data types since they were converted from text files).

There is one other table that refers to this table and is 1-1 (i.e., one record refers to one record in the column table 400).

The table is a list of files containing parameters that are "connected" to the application.

I look forward to your answers.

thanks

Answer 1

Depending on your description of the process, I would start with something like this. The model is simplified, does not capture the story, etc., but it is a good starting point. Note: parameter = property.

- Setup is a set of properties . One setting can have many properties. , one property belongs to only one setting .
- A machine can have many settings , one setting applies to only one machine .
- The property has a specific type. (temperature, runtime, spindle speed), there can be many properties of a particular type .
- Dimension and trait are types of properties . Dimension is a numeric property such as speed. Value is a descriptive property such as color or text.

Answer 2

For a wide table:

• Report this quickly, as it appears to be denormalized, and therefore no connections are required.
• It’s easy for end users to understand, because they don’t need to keep the data model in their heads.

Against having a wide table:

• You probably need to have several composite indexes to get good query performance.
• It is more difficult to maintain data consistency, i.e. it is necessary to update several lines when the data changes, if this data is on several lines.
• Because you need to update multiple rows and support multiple indexes, simultaneous performance for updates can become a problem as locks increase.
• You may have encountered records with many zeros in columns if the attribute does not apply to the entity in this row, which may complicate the processing of the results.
• If lazy developers make SELECT * from a table, you end up dragging a lot of data over the network, so you usually need to maintain suitable types of subsets.

So it all depends on what you do. If the main goal of the table is an OLAP report, and updates are infrequent and affect several rows, then perhaps a wide, denormalized table is the right thing. In an OLTP environment, this is probably not, and you prefer narrower tables. (I usually design in 3NF and then denormalize to fulfill requests when I go.)

You can always use the approach to normalizing and providing wide viewing for readers, if that is what they want to see.

Without knowing more about the situation, it is actually impossible to say more about the pros and cons in your specific circumstances.

Edit:

Given what you said in your comments, do you think you have a long and skinny table of names = value, so that you only have UserId, PropertyName, PropertyValue columns? You might want to add other meta attributes; timestamp, version, or something else. SQL Server is quite effective at working with these types of tables, so do not reduce a simple solution like this.