design questions

So, I don't have the most design experience... but thats about to change. Iv
e
got a DB that isn't in even 1st NF and has been in Production for some time.
There are hundreds of Stored Procs already and nobody is about to start
re-writing them. So, Im thinking I need to make use of Views. In this way,
existing SP's can stay the same, new SP's can be written against the new
table structures. Im sure Im not the first one to do this, so, any gotcha's?
Anything I need to have in my mind while I do the redesign so I don't spin m
y
wheels in the wrong direction and find this approach won't work?
TIA,
ChrisRChris,

> There are hundreds of Stored Procs already and nobody is about to start
> re-writing them. So, Im thinking I need to make use of Views. In this way,
> existing SP's can stay the same, new SP's can be written against the new
> table structures.
Obviously the best scenario would be a redesign to comply with 3rd NF
however it sounds like that is not going to happen. You already have an
abstraction layer to the underlying tables with stored procedures. Why do
you want to use views as well? I'm not saying that views should or should
not be used just trying to better understand your situation.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:6A3B81BB-2AE0-44BD-8BED-09D1F88E1398@.microsoft.com...
> So, I don't have the most design experience... but thats about to change.
> Ive
> got a DB that isn't in even 1st NF and has been in Production for some
> time.
> There are hundreds of Stored Procs already and nobody is about to start
> re-writing them. So, Im thinking I need to make use of Views. In this way,
> existing SP's can stay the same, new SP's can be written against the new
> table structures. Im sure Im not the first one to do this, so, any
> gotcha's?
> Anything I need to have in my mind while I do the redesign so I don't spin
> my
> wheels in the wrong direction and find this approach won't work?
> --
> TIA,
> ChrisR|||Depends what you mean by "not in 1NF". If you intend to declare new keys and
constraints then views alone won't stop the legacy code throwing constraint
violation errors. Also, not every view may be updateable.
Triggers on INSTEAD OF views might be an option but you might find that it's
easier to rewrite the procs rather than try to accommodate every one of them
in a trigger.
David Portas
SQL Server MVP
--
"ChrisR" wrote:

> So, I don't have the most design experience... but thats about to change.
Ive
> got a DB that isn't in even 1st NF and has been in Production for some tim
e.
> There are hundreds of Stored Procs already and nobody is about to start
> re-writing them. So, Im thinking I need to make use of Views. In this way,
> existing SP's can stay the same, new SP's can be written against the new
> table structures. Im sure Im not the first one to do this, so, any gotcha'
s?
> Anything I need to have in my mind while I do the redesign so I don't spin
my
> wheels in the wrong direction and find this approach won't work?
> --
> TIA,
> ChrisR|||The use of Views would be this:
Lets say we have a table called "Client":
ClientID
LName
FName
Address
Address2
City
City2
State
State2
Child1
Child2
Child3
Child4
Child5
This doesn't look too far some of the tables we have. An option would be to
Rename Client to ClientTable with only 3 columns.
ClientID
LName
FName
Another table named ClientAddress. (More than 1 address can be entered here
if needed.)
ClientID
Address
City
State
And another table called ClientChildMore than 1 child can be entered here
if needed.)
ClientID
Child
And finally, create a view named Client.
Create View Client
as
select * from ClientTable ct
inner join ClientAddress ca on ct.ClientId = ca.ClientId
inner join ClientChild cc on ct.ClientId = cc.ClientID
This is a tactic I've heard used by others, but never done it myself.
TIA,
ChrisR
"ChrisR" wrote:

> So, I don't have the most design experience... but thats about to change.
Ive
> got a DB that isn't in even 1st NF and has been in Production for some tim
e.
> There are hundreds of Stored Procs already and nobody is about to start
> re-writing them. So, Im thinking I need to make use of Views. In this way,
> existing SP's can stay the same, new SP's can be written against the new
> table structures. Im sure Im not the first one to do this, so, any gotcha'
s?
> Anything I need to have in my mind while I do the redesign so I don't spin
my
> wheels in the wrong direction and find this approach won't work?
> --
> TIA,
> ChrisR|||Chris,
I am working on a major redesign at the moment. My shop also has lots of SPs
and tightly-coupled JSP code. My plan is to redesign the database from
scratch and then refactor the database and its attendant code bit by bit so
that in a year or so we will have completely re-engineered to database. In
other words,
1. Design a new, target schema.
2. Map the old schema to the new schema.
3. Develop a plan to convert "table clusters" from the old to the new schema
.
4. Follow the plan.
For example, you may have a "company cluster" that consists of several
tables that handle company information. Convert this cluster from the old
schema to the new schema and rewrite your code base to utilize the new schem
a.
HTH,
Mike