0

I'm recreating a DB and I have a table with 150 columns and it has 700 rows currently (small dataset) - It will likely take 10 more years to get to 1000 rows.
My question: Most of my data is normalized. About 125 fields contain a single numeric value (hours, currency, decimals, and integers). There are 10 or so columns that can have multiple values.

Do I continue to use the single table with 150 Rows?

Or

Do I create cross-reference tables and use a pivot query to turn my rows into columns? Something like this:

     **c_FieldNames**          **cx_FieldValues**       **Project**
      id int identity (PK)     id int identity(1,1)       ProjID int (PK)
                               fkProjectID int            ProjectName
      FieldName nvarchar       FieldNameID int (FK to id from c_fieldNames)
      Decimals nvarchar(2)     FieldValue numeric(16,2)

The decimals would tell me how many decimal places a given field would need - I'd like to incorporate that into my query... Not sure if that's possible. For each of my 125 fields with numbers, I would create a row in the cx_FieldNames table which would get an ID. That ID would be used in the FieldNameID as a foreign key.

I would then create a view a pivot table that would create a table of the 125 rows dynamically in addition to my standard table or so rows to look like the table with 150 columns.

I'm pretty sure I will be able to use a pivot table to turn my rows into columns. (Dynamically display rows as columns)

Benefits: I could create a table for reports that would have all the "columns" I need for that report and then filter to them and just pull those fields dynamically. 

Reports
ReportID int
FieldID int

The fieldID's would be based on the c_FieldName id's and I could turn all required field names (that are in the rows) into headers and run a vast majority of reports based on dynamic sql generated based on the field names. Same applies to all data structured... [Edit from Author] The more I think about this, I could do this with either table structure, which negates the benefits I saw here, as I am adding complexity for no good reason, as pointed out in the comments.

My thought is that it will same me much development time as I can use a pivot table to generate reports and pull data on the fly without much trouble. Updating data will be a bit of a chore, but not that much more than normal. I am creating a C#.NET Website with Visual Studio (hosted on Azure) to allow users to view, update, run reports on the data. Any major drawbacks in this structure? Is this a good idea? Are 125 columns in a Pivot too many? Thanks in Advance!

Danimal111
  • 1,976
  • 25
  • 31
  • 2
    This question is quite broad and too general, but I think you are on the right way to create an over-complicated data structure with no good reason. What I would suggest is to separate those 10 or so columns that can contain multiple values from the main table, each to it's own table, referencing the main table with a foreign key. For any advice more then that, You will need to be more specific. – Zohar Peled Feb 01 '18 at 07:20
  • 1
    What do you gain by changing the table? If there is no clear answer to that question, leave it as is. – ppijnenburg Feb 01 '18 at 09:39
  • @ZoharPeled - I added what I see as the benefits. I think it would allow for ease of reporting by allowing for a single reports table that I could use to turn the rows I want in the report into column headers and have the fields for 100+ different reports in one table, instead of having to create 100 different sql sprocs and 100 links to them and 100 reports... same goes for all other update and display screens. – Danimal111 Feb 01 '18 at 12:07

0 Answers0