October 12, 2018 at 5:24 pm
Some guy at the company I'm creating a database for tells me my table needs to be redone or he will use another contractor. What is he talking about? He said I should split it up into smaller tables. He's not even a programmer!
It's for insurance enrollment.
USE [studly]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [studly].[UnivCoverage08](
[intID] [int] IDENTITY(1,1) NOT NULL,
[Univ_ID] [int] NULL,
[dtmDate] [smalldatetime] NULL,
[Open_enroll] [char](1) NULL,
[Add_fam] [char](1) NULL,
[add_fam_reason] [varchar](100) NULL,
[Add_fam_date] [char](8) NULL,
[Add_fam_other] [varchar](30) NULL,
[Delete_fam] [char](1) NULL,
[Delete_fam_date] [char](8) NULL,
[Delete_fam_reason] [char](50) NULL,
[Delete_fam_other] [varchar](50) NULL,
[Cancel_waiver] [char](1) NULL,
[Cancel_waiver_date] [char](8) NULL,
[Move] [char](1) NULL,
[Move_date] [char](8) NULL,
[DP] [char](1) NULL,
[Decline_med] [char](1) NULL,
[Decline_dent] [char](1) NULL,
[Decline_vis] [char](1) NULL,
[Decline_ins] [char](1) NULL,
[Decline_dp] [char](1) NULL,
[Decline_child] [char](1) NULL,
[Decline_covered] [char](1) NULL,
[Decline_covered_other] [char](1) NULL,
[Decline_covered_reason] [char](40) NULL,
[Enroll_med] [char](6) NULL,
[Enroll_dent] [char](6) NULL,
[Enroll_vis] [char](6) NULL,
[Enroll_self] [char](1) NULL,
[self_med] [char](1) NULL,
[self_dent] [char](1) NULL,
[self_vis] [char](1) NULL,
[PCP_self] [char](7) NULL,
[PCD_self] [char](6) NULL,
[Enroll_dp] [char](1) NULL,
[Last_name_dp] [char](20) NULL,
[MI_dp] [char](1) NULL,
[First_name_dp] [char](15) NULL,
[DOB_dp] [char](8) NULL,
[gender_dp] [char](1) NULL,
[dp_med] [char](1) NULL,
[dp_dent] [char](1) NULL,
[dp_vis] [char](1) NULL,
[PCP_dp] [char](7) NULL,
[PCD_dp] [char](6) NULL,
[Enroll_child1] [char](1) NULL,
[Last_name_child1] [char](20) NULL,
[MI_child1] [char](1) NULL,
[First_name_child1] [char](15) NULL,
[DOB_child1] [char](8) NULL,
[gender_child1] [char](1) NULL,
[Student_child1] [char](1) NULL,
[child1_med] [char](1) NULL,
[child1_dent] [char](1) NULL,
[child1_vis] [char](1) NULL,
[PCP_child1] [char](7) NULL,
[PCD_child1] [char](6) NULL,
[Enroll_child2] [char](1) NULL,
[Last_name_child2] [char](20) NULL,
[MI_child2] [char](1) NULL,
[First_name_child2] [char](15) NULL,
[DOB_child2] [char](8) NULL,
[gender_child2] [char](1) NULL,
[Student_child2] [char](1) NULL,
[child2_med] [char](1) NULL,
[child2_dent] [char](1) NULL,
[child2_vis] [char](1) NULL,
[PCP_child2] [char](7) NULL,
[PCD_child2] [char](6) NULL,
[Enroll_child3] [char](1) NULL,
[Last_name_child3] [char](20) NULL,
[MI_child3] [char](1) NULL,
[First_name_child3] [char](15) NULL,
[DOB_child3] [char](8) NULL,
[gender_child3] [char](1) NULL,
[Student_child3] [char](1) NULL,
[child3_med] [char](1) NULL,
[child3_dent] [char](1) NULL,
[child3_vis] [char](1) NULL,
[PCP_child3] [char](7) NULL,
[PCD_child3] [char](6) NULL,
[Enroll_child4] [char](1) NULL,
[Last_name_child4] [char](20) NULL,
[MI_child4] [char](1) NULL,
[First_name_child4] [char](15) NULL,
[DOB_child4] [char](8) NULL,
[gender_child4] [char](1) NULL,
[Student_child4] [char](1) NULL,
[child4_med] [char](1) NULL,
[child4_dent] [char](1) NULL,
[child4_vis] [char](1) NULL,
[PCP_child4] [char](7) NULL,
[PCD_child4] [char](6) NULL,
[Enroll_child5] [char](1) NULL,
[Last_name_child5] [char](20) NULL,
[MI_child5] [char](1) NULL,
[First_name_child5] [char](15) NULL,
[DOB_child5] [char](8) NULL,
[gender_child5] [char](1) NULL,
[Student_child5] [char](1) NULL,
[child5_med] [char](1) NULL,
[child5_dent] [char](1) NULL,
[child5_vis] [char](1) NULL,
[PCP_child5] [char](7) NULL,
[PCD_child5] [char](6) NULL,
[Benif1] [char](35) NULL,
[Benif1_relation] [char](15) NULL,
[Benif1_DOB] [char](8) NULL,
[Benif1_address] [text] NULL,
[Benif1_percent] [smallint] NULL,
[Benif2] [char](35) NULL,
[Benif2_relation] [char](15) NULL,
[Benif2_DOB] [char](8) NULL,
[Benif2_address] [text] NULL,
[Benif2_percent] [smallint] NULL,
[Benif3] [char](35) NULL,
[Benif3_relation] [char](15) NULL,
[Benif3_DOB] [char](8) NULL,
[Benif3_address] [text] NULL,
[Benif3_percent] [smallint] NULL,
[Contingent1] [char](35) NULL,
[Contingent1_relation] [char](15) NULL,
[Contingent1_DOB] [char](8) NULL,
[Contingent1_address] [text] NULL,
[Contingent1_percent] [smallint] NULL,
[Contingent2] [char](35) NULL,
[Contingent2_relation] [char](15) NULL,
[Contingent2_DOB] [char](8) NULL,
[Contingent2_address] [text] NULL,
[Contingent2_percent] [smallint] NULL,
[Contingent3] [char](35) NULL,
[Contingent3_relation] [char](15) NULL,
[Contingent3_DOB] [char](8) NULL,
[Contingent3_address] [text] NULL,
[Contingent3_percent] [smallint] NULL,
[Venom] [varchar](3) NULL,
[Ins_privacy] [varchar](3) NULL,
[marketplace] [varchar](1) NULL,
[Ins_Type] [varchar](3) NULL,
[Dent_PPO_NJ] [varchar](1) NULL,
[pdMedPrem] [smallmoney] NULL,
[univMedPrem] [smallmoney] NULL,
[pdDentPrem] [smallmoney] NULL,
[univDentPrem] [smallmoney] NULL,
[pdVisPrem] [smallmoney] NULL,
[univVisPrem] [smallmoney] NULL,
[pdLifePrem] [smallmoney] NULL,
[univLifePrem] [smallmoney] NULL,
[pdContribution] [smallmoney] NULL,
[univContribution] [smallmoney] NULL,
[Initial_enroll] [varchar](1) NULL,
[med_bup] [varchar](1) NULL,
[den_bup] [varchar](1) NULL,
[edit_by] [varchar](3) NULL,
[Privacy] [varchar](1) NULL,
[Terms] [varchar](3) NULL,
[isDWC] [smallint] NULL,
[univDWC] [smallmoney] NULL,
[pdDWC] [smallmoney] NULL,
[role_dependent1] [varchar](30) NULL,
[intFormType] [int] NULL,
[intWizard] [int] NULL,
[intWaiverStep] [int] NULL,
[mnthAmount] [money] NULL,
CONSTRAINT [PK_UnivCoverage08] PRIMARY KEY CLUSTERED
(
[intID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [studly].[UnivCoverage08] ADD CONSTRAINT [DF_UnivCoverage08_dtmDate] DEFAULT (getdate()) FOR [dtmDate]
GO
ALTER TABLE [studly].[UnivCoverage08] ADD CONSTRAINT [DF_UnivCoverage08_isDWC] DEFAULT ((0)) FOR [isDWC]
GO
ALTER TABLE [studly].[UnivCoverage08] ADD CONSTRAINT [DF_UnivCoverage08_intFormType] DEFAULT ((0)) FOR [intFormType]
GO
ALTER TABLE [studly].[UnivCoverage08] ADD CONSTRAINT [DF_UnivCoverage08_intWizard] DEFAULT ((0)) FOR [intWizard]
GO
ALTER TABLE [studly].[UnivCoverage08] ADD CONSTRAINT [DF_UnivCoverage08_intWaiverStep] DEFAULT ((0)) FOR [intWaiverStep]
GO
ALTER TABLE [studly].[UnivCoverage08] ADD CONSTRAINT [DF_UnivCoverage08_mnthAmount] DEFAULT ((0)) FOR [mnthAmount]
GO
October 12, 2018 at 6:01 pm
Unless this is a datawarehouse (and even that not a must) then lots are wrong.
See http://agiledata.org/essays/dataNormalization.html for some info - and search net for database normalization.
As for the contractor not being a developer - no need to be - and developers are the ones that normally make these type of mistakes due to lack of knowledge.
And if you were working for my company you would definitely redo the table.
October 13, 2018 at 9:57 am
Complete lack of normalisation, several questionable data types, lots of names that don't tell me anything (what is dtmDate?), no naming standard making this look like it was 'designed' by several people (and please don't prefix columns with their data types, it's unnecessary), and the table name itself suggests wider problems. There's a lot wrong with this table. Maybe ask the guy to teach you to do it better, then you both win.
I think I'll take this as an example for my grad class next year to learn from.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2018 at 11:03 am
Hmmm.... based on other posts by this op, this is all starting to sound a bit different than what the actual wording of the post might convey. Please see the following...
https://www.sqlservercentral.com/Forums/2002689/Query-slow-Help-improve?Update=1#bm2002914
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2018 at 11:04 am
frederico_fonseca - Friday, October 12, 2018 6:01 PMUnless this is a datawarehouse (and even that not a must) then lots are wrong.See http://agiledata.org/essays/dataNormalization.html for some info - and search net for database normalization.
As for the contractor not being a developer - no need to be - and developers are the ones that normally make these type of mistakes due to lack of knowledge.
And if you were working for my company you would definitely redo the table.
Nah... this would suck even for a data warehouse table.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2018 at 5:29 pm
GilaMonster - Saturday, October 13, 2018 9:57 AMComplete lack of normalisation, several questionable data types, lots of names that don't tell me anything (what is dtmDate?), no naming standard making this look like it was 'designed' by several people (and please don't prefix columns with their data types, it's unnecessary), and the table name itself suggests wider problems. There's a lot wrong with this table. Maybe ask the guy to teach you to do it better, then you both win.I think I'll take this as an example for my grad class next year to learn from.
I'm completely agree with GilaMonster!
it is not looking as for a RDBMS table, and for sure not for OLTP..
Brake the table to two or more tables and bind then with constraints (PK/FK).
I would also modify the data types...
I saw this kind of tables in OLTP BDs and had to work hard to redesign them.
Have a look at https://www.red-gate.com/simple-talk/sql/database-administration/ten-common-database-design-mistakes/
goodluck.
October 15, 2018 at 8:34 am
This is the same guy who says he's pretty good at SQL. I think I'm going to disagree. If you can't normalize tables, how can you be 'pretty good'?
October 15, 2018 at 8:56 am
SQLStud 74876 - Friday, October 12, 2018 5:24 PMSome guy at the company I'm creating a database for tells me my table needs to be redone or he will use another contractor. What is he talking about? He said I should split it up into smaller tables. He's not even a programmer!
It's for insurance enrollment.
Let me start by saying I work for an insurance company. And unless I was building this table for a report / data mart, I would be fired for putting this into a production environment. It's not just about normalization. It's about performance.
So let's talk about this whole thing.
First, the "guy at the company." Why does it matter that he's not even a programmer? I ask because if he's the one who hired you, or the manager in charge of the developers, or even just the finance guy, it's better for him to cut his losses before his company sinks too much money into a database design that will kill their system during the height of the enrollment system.
Are you programming the enrollment system or is it other people? If it's other people, have they said anything about your table / database design yet? (I think they have given other posts you've added to this site). Has it occurred to you that the programmers might be taking their concerns to him since you apparently aren't listening to them?
You're building a database, but the "guy at the company" is talking specifically about one table? How many tables are you building in this database? Just the one? Or several that have similar formats?
Do you understand the difference between "flat file databases" and relational databases? What is your vision for how this database is going to be read from and written to? Have you communicated that properly to the Development team? Do you have a set of requirements to build this database from or are you creating it by the seat of your pants because they've only given you a generic "we need a database now!" kind of direction?
October 15, 2018 at 9:48 am
I agree with the other critiques. As to "the guy", he's 100% right, and he should already run, not walk, to another contractor, without delay!
1NF is trivial and even that is violated all over the place. I taught dbs at a college, and I'd expect even sophomores to do much better than that.
You need to step back and study data design for a while ... well, for a long time actually, until it's also obvious to you what some of the egregious design flaws with what you've proposed are. Is it April 1st and somehow the rest of us missed it?!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 15, 2018 at 11:53 am
Ahhh man! You guys are a tough crowd!
October 15, 2018 at 12:10 pm
SQLStud 74876 - Monday, October 15, 2018 11:53 AMAhhh man! You guys are a tough crowd!
We are trying to help. If you can answer some of my questions, we would be better positioned to offer solutions.
October 15, 2018 at 12:19 pm
It's official, folks. The man is NOT an idiot. Rather, he's quite clever, as I suspected. Please see the following post.
https://www.sqlservercentral.com/Forums/FindPost2003341.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2018 at 12:20 pm
Jeff Moden - Monday, October 15, 2018 12:19 PMIt's official, folks. The man is NOT an idiot. Rather, he's quite clever, as I suspected. Please see the following post.
https://www.sqlservercentral.com/Forums/FindPost2003341.aspx
Yep. I just saw that.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply