One to one or One Table

[BigBlackDog]

Lets say that you have 1000 pieces of data which needed to be displayed across a few screens
You look at the data and decide that it cant be normalised
Do you create a table with 1000 columns or create a number of tables each with a one to one relationship 😕

[Roger_Ramjet]

You definitely do not create a table with 1000 columns. Are you sure that the data is normalised correctly?
Anyway, create multiple tables grouping the column according to which ones will be selected in the same query - unless of course you will be selecting all 1000???

[Sxooter]

That's a really vague question. Can you give us a concrete example?

[BigBlackDog]

Well heres the current situation
In my new job I inherited a vertical Table we decided that the vertical table wasnt going to work for us in the future.
When I looked at the table contents there was approx 1300 types of information so in the first instance creating table would give 1300 columns. When I normailsed the table I managed to cut it down to 750 Columns in the Table .
And Other people have also looked at normalising the table
For each row in the table the data will appear only once. For manageability we decided to group similar data and seperate this into tables however this is still only ever a one to one situatin so I wouldnt consider it normalised but rather that each table is an extension of the original.
But even in this form the Original Table has about 30 Columns and one of the extended tables has in excess of 200
So the question I guess im looking at , Is it better to have 1 Table with a large number of fields (750 in this case) or try to maintain a number of tables each with under 100 fields
Each Table would represent a screen in the application when working with the data
:eek:

[Piranha]

Is it possible to have it something like this instead:

[b]table page[/b]
id
possibly other columns
[b]table pagecontent[/b]
id
tableid references table(id)
content
possibly other columns

Then it is a one-to-many relationship between the tables, and you have two tables that are easy to handle in any way. I don't know if this is even possible for you since we haven't got enough information. Can you please post an example of a table with values, then it is easier to help you out.

[Sxooter]

What pirhana said...

[BigBlackDog]

SubstanceID int Unchecked
AcuteOverAllClassification nvarchar(10) Checked
AcuteOverAllIndirect bit Checked
AcuteOralClassification nvarchar(20) Checked
AcuteOralValue nvarchar(20) Checked
AcuteOralIndirect bit Checked
AcuteOralEndPoint nchar(20) Checked
AcuteOralRawData nvarchar(MAX) Checked
AcuteOralAssumption nvarchar(2000) Checked
AcuteOralKeyStudy nvarchar(MAX) Checked
AcuteDermalClassification nvarchar(10) Checked
AcuteDermalValue nvarchar(20) Checked
AcuteDermalIndirect bit Checked
AcuteDermalEndPoint nvarchar(20) Checked
AcuteDermalRawData nvarchar(MAX) Checked
AcuteDermalAssumption nvarchar(2000) Checked
AcuteDermalKeyStudy nvarchar(MAX) Checked
AcuteInhalationClassification nvarchar(10) Checked
AcuteInhalationValue nvarchar(20) Checked
AcuteInhalationIndirect bit Checked
AcuteInhalationEndPoint nvarchar(50) Checked
AcuteInhalationRawData nvarchar(MAX) Checked
AcuteInhalationAssumption nvarchar(2000) Checked
AcuteInhalationKeyStudy nvarchar(MAX) Checked
AcuteInhalationUnits nvarchar(20) Checked
AcuteInhalationForm nvarchar(20) Checked
AcuteInhalationStudyParticleSize nvarchar(20) Checked
AcuteInhalationAspirationHazard bit Checked
AcuteOtherClassification nvarchar(10) Checked
AcuteOtherRawData nvarchar(MAX) Checked
AcuteOtherAssumption nvarchar(2000) Checked
AcuteOtherKeyStudy nvarchar(MAX) Checked
AcuteOtherUnits nvarchar(50) Checked
AcuteOtherValue nvarchar(20) Checked
AcuteOtherIndirect bit Checked
AcuteOtherRoute nvarchar(20) Checked
AcuteOtherEndPoint nvarchar(50) Checked
SkinIrritationClassification nvarchar(20) Checked
SkinIrritationRawData nvarchar(MAX) Checked
SkinIrritationAssumption nvarchar(2000) Checked
SkinIrritationKeyStudy nvarchar(MAX) Checked
SkinIrritationIndirect bit Checked
SkinIrritationEffectsBelowCutOff bit Checked
EyeIrritationClassification nvarchar(20) Checked
EyeIrritationRawData nvarchar(MAX) Checked
EyeIrritationAssumption nvarchar(2000) Checked
EyeIrritationKeyStudy nvarchar(MAX) Checked
EyeIrritationIndirect bit Checked
EyeIrritationEffectsBelowCutOff bit Checked
SensitiserRespiratoryClassification nvarchar(10) Checked
SensitiserRespiratoryEffectsBelowCutOff bit Checked
SensitiserRespiratoryIndirect bit Checked
SensitiserRespiratoryRawData nvarchar(MAX) Checked
SensitiserRespiratoryAssumption nvarchar(MAX) Checked
SensitiserContactClassification nvarchar(10) Checked
SensitiserContactEffectsBelowCutOff bit Checked
SensitiserContactIndirect bit Checked
SensitiserContactRawData nvarchar(MAX) Checked
SensitiserContactAssumption nvarchar(MAX) Checked
SensitiserContactPhoto bit Checked
SensitisationKeyStudy nvarchar(MAX) Checked
MutagenClassification nvarchar(10) Checked
MutagenIndirect bit Checked
MutagenEffectsBelowCutOff bit Checked
MutagenRawData nvarchar(MAX) Checked
MutagenAssumption nvarchar(MAX) Checked
MutagenKeyStudy nvarchar(MAX) Checked
CarcinogenClassification nvarchar(10) Checked
CarcinogenIndirect bit Checked
CarcinogenEffectsBelowCutOff bit Checked
CarcinogenRawData nvarchar(MAX) Checked
CarcinogenAssumption nvarchar(MAX) Checked
CarcinogenKeyStudy nvarchar(MAX) Checked
ReproductiveABClassification nvarchar(20) Checked
ReproductiveABIndirect bit Checked
ReproductiveABRawData nvarchar(MAX) Checked
ReproductiveABAssumption nvarchar(MAX) Checked
ReproductiveABKeyStudy nvarchar(MAX) Checked
ReproductiveABEffectsBelowCutOff bit Checked
ReproductiveABValue nvarchar(20) Checked
ReproductiveABEndPoint nvarchar(20) Checked
ReproductiveABUnits nvarchar(20) Checked
ReproductiveCClassification nvarchar(10) Checked
ReproductiveCIndirect bit Checked
ReproductiveCRawData nvarchar(MAX) Checked
ReproductiveCAssumption nvarchar(MAX) Checked
ReproductiveCKeyStudy nvarchar(MAX) Checked
ReproductiveCEffectsBelowCutOff bit Checked
ReproductiveCValue nvarchar(20) Checked
ReproductiveCEndPoint nvarchar(20) Checked
ReproductiveCUnits nvarchar(20) Checked
OrganOverAllClassification nvarchar(20) Checked
OrganOralClassification nvarchar(10) Checked
OrganOralValue nvarchar(20) Checked
OrganOralUnits nvarchar(20) Checked
OrganOralIndirect bit Checked
OrganOralEndPoint nvarchar(20) Checked
OrganOralRawData nvarchar(MAX) Checked
OrganOralAssumption nvarchar(MAX) Checked
OrganOralKeyStudy nvarchar(MAX) Checked
OrganOralWHOADIValue nvarchar(20) Checked
OrganOralWHOADIUnits nvarchar(20) Checked
OrganOralWHOADIChosenInternationalValue bit Checked
OrganOralUSEPAREDSRFDValue nvarchar(20) Checked
OrganOralUSEPAREDSRFDUnits nvarchar(30) Checked
OrganOralUSEPAREDSRFDChosenInternationalValue bit Checked
OrganOralUSEPAIRISRFDValue nvarchar(20) Checked
OrganOralUSEPAIRISRFDUnits nvarchar(20) Checked
OrganOralUSEPAIRISRFDChosenInternationalValue bit Checked
OrganOralColEPARFDValue nvarchar(20) Checked
OrganOralColEPARFDUnits nvarchar(20) Checked
OrganOralColEPARFDChosenInternationalValue bit Checked
OrganOralASTDRRFDValue nvarchar(20) Checked
OrganOralASTDRRFDUnits nvarchar(20) Checked
OrganOralASTDRRFDChosenInternationalValue bit Checked
OrganOralAusRFDADIValue nvarchar(20) Checked
OrganOralAusRFDADIUnits nvarchar(20) Checked
OrganOralAusRFDADIChosenInternationalValue bit Checked
OrganOralOtherRFDADIValue nvarchar(20) Checked
OrganOralOtherRFDADIUnits nvarchar(30) Checked
OrganOralOtherRFDADIChosenInternationalValue bit Checked
OrganOralInternationalValueRawData nvarchar(MAX) Checked
OrganOralPrimaryTargetOrganID int Checked
OrganDermalClassification nvarchar(10) Checked
OrganDermalValue nvarchar(20) Checked
OrganDermalUnits nvarchar(50) Checked
OrganDermalIndirect bit Checked
OrganDermalEndPoint nvarchar(50) Checked
OrganDermalRawData nvarchar(MAX) Checked
OrganDermalAssumption nvarchar(MAX) Checked
OrganDermalKeyStudy nvarchar(MAX) Checked
OrganDermalWHOADIValue nvarchar(20) Checked
OrganDermalWHOADIUnits nvarchar(20) Checked
OrganDermalWHOADIChosenInternationalValue bit Checked
OrganDermalUSEPAREDSRFDValue nvarchar(20) Checked
OrganDermalUSEPAREDSRFDUnits nvarchar(20) Checked
OrganDermalUSEPAREDSRFDChosenInternationalValue bit Checked
OrganDermalUSEPAIRISRFDValue nvarchar(20) Checked
OrganDermalUSEPAIRISRFDUnits nvarchar(20) Checked
OrganDermalUSEPAIRISRFDChosenInternationalValue bit Checked
OrganDermalColEPARFDValue nvarchar(20) Checked
OrganDermalColEPARFDUnits nvarchar(20) Checked
OrganDermalColEPARFDChosenInternationalValue bit Checked
OrganDermalASTDRRFDValue nvarchar(20) Checked
OrganDermalASTDRRFDUnits nvarchar(20) Checked
OrganDermalASTDRRFDChosenInternationalValue bit Checked
OrganDermalAusRFDADIValue nvarchar(20) Checked
OrganDermalAusRFDADIUnits nvarchar(20) Checked
OrganDermalAusRFDADIChosenInternationalValue bit Checked
OrganDermalOtherRFDADIValue nvarchar(20) Checked
OrganDermalOtherRFDADIUnits nvarchar(20) Checked
OrganDermalOtherRFDADIChosenInternationalValue bit Checked
OrganDermalInternationalValueRawData nvarchar(MAX) Checked
OrganDermalPrimaryTargetOrganID int Checked
OrganInhalationClassification nvarchar(10) Checked
OrganInhalationValue nvarchar(20) Checked
OrganInhalationUnits nvarchar(20) Checked
OrganInhalationIndirect bit Checked
OrganInhalationEndPoint nchar(10) Checked
OrganInhalationRawData nvarchar(MAX) Checked
OrganInhalationAssumption nvarchar(MAX) Checked
OrganInhalationKeyStudy nvarchar(MAX) Checked
OrganInhalationWHOADIValue nvarchar(20) Checked
OrganInhalationWHOADIUnits nvarchar(20) Checked
OrganInhalationWHOADIChosenInternationalValue bit Checked
OrganInhalationUSEPAREDSRFDValue nvarchar(20) Checked
OrganInhalationUSEPAREDSRFDUnits nvarchar(20) Checked
OrganInhalationUSEPAREDSRFDChosenInternationalValue bit Checked
OrganInhalationUSEPAIRISRFDValue nvarchar(20) Checked
OrganInhalationUSEPAIRISRFDUnits nvarchar(20) Checked
OrganInhalationUSEPAIRISRFDChosenInternationalValue bit Checked
OrganInhalationColEPARFDValue nvarchar(20) Checked
OrganInhalationColEPARFDUnits nvarchar(20) Checked
OrganInhalationColEPARFDChosenInternationalValue bit Checked
OrganInhalationASTDRRFDValue nvarchar(20) Checked
OrganInhalationASTDRRFDUnits nvarchar(20) Checked
OrganInhalationASTDRRFDChosenInternationalValue bit Checked
OrganInhalationAusRFDADIValue nvarchar(20) Checked
OrganInhalationAusRFDADIUnits nvarchar(20) Checked
OrganInhalationAusRFDADIChosenInternationalValue bit Checked
OrganInhalationOtherRFDADIValue nvarchar(20) Checked
OrganInhalationOtherRFDADIUnits nvarchar(20) Checked
OrganInhalationOtherRFDADIChosenInternationalValue bit Checked
OrganInhalationInternationalValueRawData nvarchar(MAX) Checked
OrganInhalationPrimaryTargetOrganID int Checked
OrganOtherClassification nvarchar(20) Checked
OrganOtherValue nvarchar(20) Checked
OrganOtherUnits nvarchar(20) Checked
OrganOtherIndirect bit Checked
OrganOtherEndPoint nvarchar(10) Checked
OrganOtherRawData nvarchar(MAX) Checked
OrganOtherAssumption nvarchar(MAX) Checked
OrganOtherKeyStudy nvarchar(MAX) Checked
OrganOtherPrimaryTargetOrganID int Checked
MetallicCorrosiveValue nvarchar(20) Checked
MetallicCorrosiveClassification nvarchar(10) Checked
MetallicCorrosiveIndirect bit Checked
MetallicCorrosiveRawData nvarchar(MAX) Checked
MetallicCorrosiveAssumption nvarchar(MAX) Checked
MetallicCorrosiveKeyStudy nvarchar(MAX) Checked
ModeOfActionID int Checked
ModeOfActionRawData nvarchar(MAX) Checked
ToxicoRawData nvarchar(MAX) Checked
ToxicoAssumptions nvarchar(MAX) Checked
AuditedByStaffID int Checked
ToxicityAuditedByStaffID int Checked
ToxicityAuditedDate datetime Checked
Defatter bit Checked

[Roger_Ramjet]

Yeah, cool. Now how about some data so we can show you how this should be done.

[BigBlackDog]

The data can be anything you want
Some of the data fields are nvarchar(max) so are big others are just bit fields