SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 Twit List
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 88

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/08/2010 :  15:32:11  Show Profile  Reply with Quote
I just had to nominate this. This table comes from our vendor, experts in insurance and data base design. The table name is "Policy". At least it doesn't have any nText fields.

Jim


data_pk
pk
RootDCTID
RootDCTKey
id
EffectiveDate
ExpirationDate
LineOfBusiness
Term
Product
HonorRates
AuditPeriod
SICCode
SICCodeDesc
NAICSCode
NAICSCodeDesc
ReasonForDeclination
PrimaryRatingState
OriginalPolicyNumber
AuditType
TakeOutPolicyIndicator
AccountNumberSelection
CrossReferenceSelection
PriorPolicyNumber
ConversionPolicyType
AccommodationCode
ProcessingCenter
PremiumPayor
AgencyPrefix
producer
Underwriter
BranchCode
TowerCode
ReceivedDate
CoderID
QuoteStatusCodes
Reason
DeclinationLetterReason
Agency
AgencyDetailAddress1
AgencyDetailAddress2
AgencyDetailCity
AgencyDetailFax
AgencyDetailName
AgencyDetailPhone
AgencyDetailState
AgencyDetailZip
PICSReason
GoverningClassCode
PICSPolicyChangeEffectiveDate
PICSPolicyChangeExpirationDate
RenewalPolicyIndicator
NCCIStateToCompare
DividendProgramIndicator
DepositPercent
DepositPercentageCalc
DepositPercentOverride
FinanceCompany
FinanceCompanyName
FinanceCompanyNumber
Installment
BillType
PaymentPlan
BillingAddress
DepositAmount
QuoteNumber
TermFactor
PremiumPrior
PremiumWritten
Premium
CancellationDate
CancellationDays
Description
PolicyNumber
PremiumChange
Status
TransactionDate
TransactionDateTime
PreviousPolicyNumber
InceptionDate
MinimumPremiumWritten
PurePremium
PurePremiumChange
PurePremiumPrior
PurePremiumWritten
TaxesSurcharges
TaxesSurchargesChange
TaxesSurchargesPrior
TaxesSurchargesWritten
DCT_FORMLIST
DCT_NAMELIST
DCT_JOBNAMES
Audited
AccountNumber
UnderwriterName
V4Callout
V4Checkbox
UserType
TermMonths
AgencyName
ProductSelection
QuoteID
TaxesSurchargesDepositAmount
AgencyCode
CommissionPercentage
PICSCallout
PremiumPriorMinusPremiumChange
PolicyOutputTransactionPremium
Comment
UserEmailAddress
ReferralReason
ReferralDescription
LossInformation
LossControlRecommendations
IssuanceInstructions
MiscellaneousInformationNotes
ReturnToUnderwriterBeforeMailing
ReturnToUnderwriterAfterMailing
ReinsuranceType
AttachmentPoint
RiskGrade
OccupancyTypeProperty
PackageModifierSelect
MCNumber
FormE
DefaultPackageWritingCompany
NatureOfOperation
PrimaryRatingStateTemp
TransactionTypeHold
ReferTrue_BindFalse
AssignedRisk
SubmissionStatus
DeclinationCode
DidNotQuoteCode
LostCode
FEINRequest
LossRunPeriod
LossRunRequest
NoLossLetterRequest
NoLossLetterPeriod
OfficerRejectionRequest
SignedApplicationRequest
VehicleListRequest
XModWorksheetRequest
DeductibleElectionRequest
DriverListRequest
DrugFreeCertificateRequest
EmployeeCountRequest
OtherRequest
OtherItem
GLGeneralLiabilityCoverage
GLPremium
GLCheckbox
GLPremiumInternalUsers
GLFinalPremium
GLFinalPremiumInternalUsers
TruckersGLDefaultTerrorismPremium
CargoCoverage
LegalLiability
OwnersGoods
Limit
Deductible
OtherLimit
ManualPremium
FormsOutputCA9990AmountPerDay
FormsOutputCA9990Maximum
FormsOutputCA9990Premium
WebRaterProducer
WebRaterUsername
PaymentPlanDescription
V4CodeCoverage
CargoCoverageTemp
PICSResendIndicator
EntityTypeBerkshireCode
ManualPremiumForReferrals
ThisQuoteIsWebRater
MinimumPremiumEarned
LossControlRecommendationsInFile
IssuanceInstructionsInFile
MiscellaneousInformationNotesInFile
PolicyInput.AutoPolicyNumber
WCPolicyNumber
UmbrellaPolicyNumber
PackagePolicyNumber
AutoPolicyNumber
V4CodeProgramCode
BillingPrivateAgencyType
TempPaymentPlanDescription
BillTypePrevious
PaymentPlanTableNamePrevious
AgencyFilter
MidtermChange
ClearMidtermChange
IsChangeToMidtermDescriptionSendToFLS
WebRaterAddProducer
WebRaterAddUsername
WebRaterAddUserPhone
WebRaterAddUserEmail
UnderwriterAddress
UnderwriterCityStateZip
PolicyPrivateUnderwriterEmail
UnderwriterFax
PolicyPrivateUnderwriterName
UnderwriterPhone
PolicyPrivateUnderwriterPhonePlusExtension
WebRaterHelpPageGoTo
RunAdditionalOtherInterestName
VINMessage
PolicyPrivateV4FailureAlert
CPPFormsManuscriptID
CPPRatingManuscriptID
InterlineFormsManuscriptID
InterlineFormsControlManuscriptID
CPPProductManuscriptID
AKFormsManuScriptID
ALFormsManuScriptID
ARFormsManuScriptID
AZFormsManuScriptID
CAFormsManuScriptID
COFormsManuScriptID
CTFormsManuScriptID
DCFormsManuScriptID
DEFormsManuScriptID
FLFormsManuScriptID
GAFormsManuScriptID
HIFormsManuScriptID
IAFormsManuScriptID
IDFormsManuScriptID
ILFormsManuScriptID
INFormsManuScriptID
KSFormsManuScriptID
KYFormsManuScriptID
LAFormsManuScriptID
MAFormsManuScriptID
MDFormsManuScriptID
MEFormsManuScriptID
MIFormsManuScriptID
MNFormsManuScriptID
MOFormsManuScriptID
MSFormsManuScriptID
MTFormsManuScriptID
MultiStateFormsManuScriptID
NCFormsManuScriptID
NDFormsManuScriptID
NEFormsManuScriptID
NHFormsManuScriptID
NJFormsManuScriptID
NMFormsManuScriptID
NVFormsManuScriptID
NYFormsManuScriptID
OHFormsManuScriptID
OKFormsManuScriptID
ORFormsManuScriptID
PAFormsManuScriptID
RIFormsManuScriptID
SCFormsManuScriptID
SDFormsManuScriptID
TNFormsManuScriptID
TXFormsManuScriptID
UTFormsManuScriptID
VAFormsManuScriptID
VTFormsManuScriptID
WAFormsManuScriptID
WIFormsManuScriptID
WVFormsManuScriptID
WYFormsManuScriptID
RiskClearanceWCRiskClearanceCallout
V4ReasonCode
V4ProRateFactor
V4CancellationFactorType
V4PreEvent
CurrentLineID
BranchCodeAgent
UnderwriterNameAgent
TowerCodeAgent
BranchCodeCaption
CoderIDCaption
TowerCodeCaption
TerrorismSelectNB
TerrorismSelectEndorsement
LastSicCodeSet
LastTransactionTypeNotDeprecated
LastTransactionFutureTransaction
LastTransactionState
CurrentTransaction
Header
TransactionDescription
LastTransactionType
LastTransactionStatus
LastTransactionDeprecatedBy
CurrentTransactionProcessingDataState
LastTransactionLastStatusChangeUser
LastTransactionStatusChangeUser
LastTransactionLastStatus
LastTransactionTypeCaption
AuditTypeDisplay
SICCodeSearchValue
SICCodeLabel
TypeOfRisk
HonoredProductManuscriptID
ISOLicenseInfo
NAICSCodeLabel
NAICSCodeSearchValue
LineNonAvailableDisplay
FormsPrintJob
OSTRenewalOverrideStatus
SelectedLines
PrimaryLocationState
FormsSCUMPrintJob
WebRaterUserNameChangeStore
EmailLink
PolicyOutputNonShreddedWebRaterEntryIndicator
PolicyOutputNonShreddedTermsOfUse
PolicyOutputNonShreddedTermsOfUseWelcome
PolicyOutputNonShreddedTermsOfUseCompanyAssumesRisk
PolicyOutputNonShreddedTermsOfUseCompanyAssumesRiskA
PolicyOutputNonShreddedTermsOfUseCompanyAssumesRiskB
PolicyOutputNonShreddedTermsOfUseCompanyAssumesRiskC
PolicyOutputNonShreddedTermsOfUseCompanyAssumesRiskD
PolicyOutputNonShreddedTermsOfUseCompanyAssumesRiskE
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics1
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics2
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics3
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics4
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics5
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics6
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics7
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics8
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics9
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics10
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics11
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics12
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics13
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics14
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics15
PolicyOutputNonShreddedTermsOfUseRiskCharacteristics16
PolicyOutputNonShreddedTermsOfUseUseAccord
WCWebRaterBindPastDateMessage
LineNonAvailableDisplayLOB
CommercialAutoComment
GeneralLiabilityComment
InlandMarineComment
CommercialPropertyComment
ErrorsAndOmissionsComment
LawEnforcementComment
CommercialUmbrellaComment
LossControlRecommendationsDateOrdered
IssuanceInstructionsDateOrdered
MiscellaneousInformationNotesDateOrdered
DoYouNeedToAddAnyGeneralLiabilityAdditionalInterests
DoYouNeedToAddAnyCommercialPropertyAdditionalInterests
HasBlanketPolicy
BillingInformationCompleted
Modify
WebRaterAddUserExt
FaxEmailSelectedInicator
ShowFollowUpInformationRequestButton
CPP_TrmOrCnd01_NuclearEnergyEndorsement
CPP_TrmOrCnd02_PunitiveDamagesExclusion
CPP_TrmOrCnd03_ExclusionComputerRelatedLosses
CPP_TrmOrCnd04_TerrorismExclusion
CPP_TrmOrCnd05_SexualAbuseMolestationExclusion
CPP_TrmOrCnd06_FungiOrBacteriaExclusion
CPP_TrmOrCnd07_WarOrTerrorismExclusion
PolicyPrivateDetailButtonClicked
WebRaterAgentHasViewedQuoteLetter
CountAdditionalOtherInterestName
WebRaterWCViewBinderHasBeenClicked
ShowVINMessage
ShowVINMessage2
ShowBindingButtonIfAllRisksHaveVIN
PolicyPrivatePICSHasBeenClickedIndicator
HowManyRisksDoNotHaveVIN
AnyRisksWithoutVIN
RunClassCodeRequest
AddYearsInBusiness
ModifyButtonClicked
PolicyPrivateIsNotAgentWorksheet
AgentHasVisitedOrStarted
PolicyPrivateWebRaterPrintQuoteIndicator
RiskClearanceRiskClearanceButtonClicked
RiskClearanceRiskClearanceCheck
RiskClearanceShredButtonClicked
RiskClearanceIsOverridden
V4ReCancel
V4DoNotRenewPolicy
V4RescindPolicy
PurePremiumWrittenChangeIndicator
PurePremiumWrittenChangeIndicatorSetValue
FlatCharge
RiskLevelChange
PICSCancellationMailedToInsuredDateValue
ReferTrue_BindFalseProperty
ReferTrue_BindFalseGL
FormsCompleted
ApplicationCompleted
CurrentTransactionDate
LastTransactionEffectiveDate
LastTransactionScheduleDate
LastTransactionProductIsReadOnly
InSICEditMode
TrueISOPackage
InNAICSEditMode
PackagePolicyIndicator
AuditMode
PreTransactionProductActivitiesCompleted
LastTransactionInterimAudit
PolicyNumberRewrite
LastTransactionFinalAudit
HidePICSButton
PICSCalloutSuccessStatus
LastTransactionCancelFlatRate
LastTransactionCancelProRate
LastTransactionCancelShortRate
LastTransactionNCCIResendNew
LastTransactionNCCIResendRenew
LastTransactionNCCIResendEndorse
CreateBinderButtonHasBeenClicked
WCWebRaterViewBinderHasBeenClicked
V4CalloutFailureIndicator
InOutOfSequence
InternalIsRefer
InternalIsReferCheck
BAP00DIndicator
NextAvailableLocationNumberUnlocked
InternalIsReferCheckGL
LastTransactionFlatCharge
FirstOutOFSequence
OccupancyEditMode
OccupancyAddMode
SumOfCovGL
PurePremiumWrittenChange
PurePremiumIterator
CommercialAutoPreviousCancellationAverageDriverFactor
PremiumMinusTerrorism
PropertyMinimumPremium
TermDays
DisplayPremiumChange
DisplayPremiumPrior
DisplayPremiumWritten
PremiumWrittenWebRaterDisplay
CustomUnearnedProRateFactor
BAP00DPreviousPremium
TotalCargo
GeneralLiabilityProperty
GeneralLiability
TotalTerrorismPremiumMinimumApplied
TotalTerrorismPremiumProperty
TotalTerrorismPremiumMinimumAutoApplied
PremiumWithTerrorism
PremiumWithoutTerrorism
DoYouNeedToAddAnyCommercialAutoAdditionalInterests


Everyday I learn something that somebody else already knew
Go to Top of Page

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 12/08/2010 :  15:53:23  Show Profile  Visit robvolk's Homepage  Reply with Quote
Really, unless they were ALL ntext columns then you have nothing to complain about.

I like how they have "data_pk" and "pk". AND "id". Normally I'd ask if they form a composite primary key but I'm almost positive this table doesn't have one.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/08/2010 :  16:24:55  Show Profile  Reply with Quote
Looks like they spent a lot of time normalizing that design.

Is that the only table in the database?




CODO ERGO SUM
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 12/08/2010 :  16:26:22  Show Profile  Reply with Quote
Does that all fit in one 8K page?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/08/2010 :  16:57:48  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
What's the max number of columns in excel nawadays?
Do you have a printer that can cope with very wide paper?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 12/08/2010 :  17:03:36  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
What's the max number of columns in excel nawadays?
16,384 columns x 2^20 rows for version 2007 and higher. I can't remember but I think Powerpivot increases those limits.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/09/2010 :  07:43:07  Show Profile  Reply with Quote
There are 163 tables in the database, including
extraexpensedependentpropertymanufacturinglocations
which has this column
extraexpensedependentpropertymanufacturinglocations.

Any guesses as to the data type?

Jim




And here's our coverage table. the tableName_pk key in each table means that it links to the pk of that table. My favorite is the coverage table, which has a coverage_pk as well as a pk

Everyday I learn something that somebody else already knew
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/09/2010 :  07:45:33  Show Profile  Reply with Quote
"Any guesses as to the data type?"

CAST NText AS Bit ??
Go to Top of Page

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 12/09/2010 :  08:01:46  Show Profile  Visit robvolk's Homepage  Reply with Quote
I'm gonna go with image, because I'm betting they store PDFs of ExtraExpenseDependentPropertyManufacturingLocations in there.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/09/2010 :  08:19:48  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
At least they were consistent with the name.
I can understand the plurality of the table name - they think of it as a collection rather than container.
Why is the column name plural though.
Rob could be right - each row contains PDFs rather than PDF
(This started off complaining about his statement but then I realised it's correct)

Maybe the table name should be
ExtraExpenseDependentPropertyManufacturingLocationss

The rows being a collection of
ExtraExpenseDependentPropertyManufacturingLocations

Each row being a collection of
ExtraExpenseDependentPropertyManufacturingLocation

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nigelrivett on 12/09/2010 08:21:57
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/09/2010 :  08:25:00  Show Profile  Reply with Quote
I saw the plurality naming of the column too - which was what made me think it should be a boolean - however, now I come to think of it, I am suret hey would have put "Has" on the front of the column name if it was a boolean
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/09/2010 :  08:26:09  Show Profile  Reply with Quote
"ExtraExpenseDependentPropertyManufacturingLocationss"

ExtraExpenseDependentPropertyManufacturingLocationLocations ??

Tell me its not a collection of PDFs stored in XML datatype?

Edited by - Kristen on 12/09/2010 08:26:52
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/09/2010 :  08:33:17  Show Profile  Reply with Quote
Its an ntext column! I asked the property manager what it is and he said "No idea". And the table is as is in the database extraexpensedependentpropertymanufacturinglocations - no caps, no nothing.


quote:
What's the max number of columns in excel nawadays?
Do you have a printer that can cope with very wide paper?



I hooked my printer directly to the rolls of toilet paper in the bathroom.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/09/2010 :  08:57:51  Show Profile  Reply with Quote
Wow! Have they got a schema called extraexpensedependentpropertymanufacturinglocations too?

That would be just as verbose as Cobol!

SELECT extraexpensedependentpropertymanufacturinglocations
FROM extraexpensedependentpropertymanufacturinglocations.extraexpensedependentpropertymanufacturinglocations.extraexpensedependentpropertymanufacturinglocations

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/09/2010 :  09:13:30  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Needs something a bit more obvious than a dot as a separator.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/09/2010 :  09:35:18  Show Profile  Reply with Quote
SELECT [extraexpensedependentpropertymanufacturinglocations]
FROM [extraexpensedependentpropertymanufacturinglocations].[extraexpensedependentpropertymanufacturinglocations].[extraexpensedependentpropertymanufacturinglocations]

Better?
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/09/2010 :  09:39:21  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Problem solved - design is ok after all.

Using the single problem flag that gets set to false whenever a problem is solved.
I've been to meetings like this
these are the problems
1
2
3
4
5

Hold on we've dealt with 5 already.
That's ok then meeting over.
..... er excuse me...

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/09/2010 :  10:29:09  Show Profile  Reply with Quote
quote:
Originally posted by nigelrivett
...Do you have a printer that can cope with very wide paper?


An inkjet plotter will do the job, printing down instead of across.

May be a little hard to read if your spreadsheet is a 50 foot long roll of paper.







CODO ERGO SUM
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/09/2010 :  10:38:47  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
quote:
Originally posted by Michael Valentine Jones
May be a little hard to read if your spreadsheet is a 50 foot long roll of paper.



That's what corridors are for.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 12/09/2010 :  11:46:42  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Originally posted by Michael Valentine Jones
May be a little hard to read if your spreadsheet is a 50 foot long roll of paper.
Ahhhh, the good ol' days of green and white fanfold paper, 11 x 17 inches. And daisywheel printers. I remember working through 3 inch stacks of that stuff.
Go to Top of Page
Page: of 88 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000