Changing the database structure later

In the starting if we take a column as not null, say there are thousands of stored procedures and many hundred tables and we have written a lot of reports based on these columns. Now, say in future, we got some requirement to make the column to accept the null values and that column also participate in joins in several queries, obviously the developer must have been written them as INNER Joins. What is the best technique to make it flexible. View? or writing everything using LEFT joins can not be a good idea.
more ▼

asked Oct 16, 2012 at 12:56 PM in Default

vipin001 gravatar image

40 2 2 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

If there is a fundamental change in design, you have to take that into account as part of the design process. You don't want to go through attempting to optimize all of your code for "what if" scenarios. If you know now that this change will occur, absolutely, then I'd go ahead and code for it now. If you're just speculating, leave everything in an optimal position and don't try to modify stuff, obfuscate behind views, or anything else until you have 100% certainty that something is needed.

Then, if you absolutely do have to make the change, I very strongly recommend biting the bullet and taking the time and effort that the change requires. Any degree of hiding the process is going to add issues to your system with more and more repercussions down the line. This is the voice of experience talking.
more ▼

answered Oct 16, 2012 at 02:35 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left

We have been through this but with a slightly different problem. The cost of the re-development was so much that we rename the main tables, created the VIEWs with table names and everything was done.

Having said that, if there are INNER JOINs in the procedures then I am afraid you would need to change the stored procedures anyways....? It is still above my head how would you deal with it in the VIEW.......?

Yes, for NOT IN statements it would matter....
more ▼

answered Oct 16, 2012 at 01:19 PM

Sacred Jewel gravatar image

Sacred Jewel
1.7k 2 4 5

FYI, the more that I think about my solution, the less that I like it so I've removed it. ;-)
Oct 16, 2012 at 02:00 PM JohnM
(comments are locked)
10|1200 characters needed characters left
If we encapsulate this in a View and use the view everywhere in reports and procedure then changing the INNER JOIN to LEFT join in a single place, we can avoid the problem of not returning the rows to the calling process because on unmatched rows as we have the column as nullable now.
more ▼

answered Oct 16, 2012 at 01:29 PM

vipin001 gravatar image

40 2 2 2

As I said we have used the same technique..... but are you saying you would encapsulte all the JOINs in Views or are you going replace INNER JOIN with LEFT JOIN everywhere.....? May be I didn't get it what you mean...?
Oct 16, 2012 at 01:38 PM Sacred Jewel
e.g Country Master say a country is consisting of states and state further divided into cities. This is valid to use inner joins to relate these three identities. But at some later time someone says that this new city does not belong to any state but still the part of country and our queries have inner joins which will not let it returns to so many reports and other part of application even if we allow creating the city without state ID. What will be the best design to change it later with less efforts?
Oct 16, 2012 at 01:47 PM vipin001
So it is a deisgn issue.... There may be various ways to handle it. One way could be to have a "NO-STATE" value against all the countries and then give the respective StateIds to the cities which do not have any state. This way you can join the missing link...... ;-)
Oct 16, 2012 at 02:06 PM Sacred Jewel
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 16, 2012 at 12:56 PM

Seen: 582 times

Last Updated: Oct 16, 2012 at 02:35 PM