For the record, no I dont have a ton of design experience. So in advance
please forgive my ignorance on this matter. If I was an expert I wouldnt need
to post questions here. I have been tasked with redesigning some of our DB's.
The catch of course is that no existing code should need to change. So I am
planning on having Views "sheild" the changes. That is, change the table
structures but still have Views look like the old structures. The following
is a prime example of what Im up against:


CREATE TABLE [dbo].[Reads] (
    [Read_ID] [int] IDENTITY (1, 1) NOT NULL ,
    [Case_ID] [int] NOT NULL ,
    [Case_Status] [bit] NULL ,
    [Target_Read] [bit] NOT NULL ,
    [Pos_No] [int] NOT NULL ,
    [NRC_FS] [int] NULL ,
    [ADC_FS] [int] NULL ,
    [NRC_CA] [int] NULL ,
    [ADC_CA] [int] NULL ,
    [NRC_MA] [int] NULL ,
    [ADC_MA] [int] NULL ,
    [FS_Ap_Date] [smalldatetime] NULL ,
    [FS_Dis_Date] [smalldatetime] NULL ,
    [CA_Ap_Date] [smalldatetime] NULL ,
    [CA_Dis_Date] [smalldatetime] NULL ,
    [MA_Ap_Date] [smalldatetime] NULL ,
    [MA_Dis_Date] [smalldatetime] NULL ,
    [Site_code_id] [int] NULL ,
    [Read_Date] [smalldatetime] NULL ,
    [Disposition_Date] [smalldatetime] NULL ,
    [Due_Date] [smalldatetime] NULL ,
    [Conference_Date] [smalldatetime] NULL ,
    [T_R_LO] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Hospital_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Reads] ADD
    CONSTRAINT [PK_Reads] PRIMARY KEY  NONCLUSTERED
    (
        [Read_ID]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO


This table actually has many more columns, but for demo purposes I shortened
it up. As you see Im there are lots of NULLS here(Land O' NULLS, NULLVille,
NULLFest05, NULL-O-Rama, etc. :-) ). A Read actually represents a write. When
data is entered for future reads. Columns 6-11 (NRC_FS - ADC_MA) are a
perfect scenario of what I think I should need to change. Each of these
columns can have a value of 0,1,2, or 3. I have ideas on how to fix this, but
I don't think they're good ones. As mentioned I don't have the luxury of any
existing code changing. Can someone please give me ideas on how to fix this?

TIA, ChrisR.

ChrisR wrote:
Show quoteHide quoteWhat is the goal of your redesign? I don't think I can give you much help
just based on this DDL. Good design requires an understanding of the real
scenario you are modelling and of your business rules. I know nothing of
those in your case.

You say that you don't have much design experience so maybe you should
suggest to your boss that either he send you on a course or that he finds
someone with more experience. Sorry I can't be more helpful, but it's
extremely difficult to come up with detailed design solutions in an online
discussion.

While I dont have much experience, Im also not in a huge rush, so I can make
mistakes along the way as long as I get it done. I have been to a course and
read some books, but dont have much in the way of practical experience for
redesigning something already in production.
Anyways, the goal here is to speed everything up(and selfishly Id like to
learn how to accomplish this task). As for the rules, the only thing Im clear
on is the value of "1" can mean something different for each of the 6
columns, and  I see your point of it being tough to accomplish this online,
but thought Id throw it out there.

Show quoteHide quote

ChrisR wrote:
Look for the places where performance is suffering and address those
points first. Use profiler to capture the most frequently executed and
longest running queries and look for indexes or stats you can use to
tune those queries.

Do your apps make use of stored procs? If so, look for cursors that can
be replaced with set-based code. Look for implicit conversions or
similar problems that are causing sub-optimal execution plans. There
may be a lot you can change without having to change the client
application code.

Table design changes may be one possibility but the first goal of good
design is integrity rather than performance. Performance is often a
side-effect of good design and conversely bad design is often a cause
of poor performance but on the information you've given it isn't
entirely obvious that the logical design should be the first place to
look to improve performance.

Then maybe something simple as investing in more hardware could
theoretically solve your problem.

Live and learn, if you have the time and can afford to make mistakes
along the way you've got a head start. My experience with re-designing
something which is already in production is that it's hard and
sometimes completly starting over and redesigning, rebuilding from
scratch with a good data load from old to new may be the better option.


from your OP:
Investigate how much time you will need to spend and prepare an
alternative plan where you do change the code, what if this is more
quickly realized and more efficient at the same time?

Can you elaborate a bit on what you're thinking about regarding your
redesign?

regards,

Gerard

compare '002' and '00²'
Evaluation of conditions
complex and large triggers
Table design question on LARGE table
Query help