Loading ...
Sorry, an error occurred while loading the content.

Re: [agileDatabases] How Do You Do Complex Table Renaming?

Expand Messages
  • Keith Ray
    Can you tell us approximately how many lines of code would have to changed to rename these columns? (Application code [in what languages? Java?] and Database
    Message 1 of 15 , Aug 2 6:29 AM
    • 0 Attachment
      Can you tell us approximately how many lines of code would have to
      changed to rename these columns? (Application code [in what
      languages? Java?] and Database Scripts)

      On 8/1/06, rickmcq10 <rmcquillin@...> wrote:
      > Table renaming is a simple refactoring, right? Well ...
      >
      > ... we have decided to call our customers "clients" to reflect their
      > true nature to our company. The problem is that the standard
      > abbreviation "Cust" is used extensively throughout our database.
      >
      > * Eight tables use the name "Cust" (tCust, tCustAgent,
      > tCustLocation, tCustPayment, ... )
      >
      > * Our standard table prefixes use "cust" (as
      > in "cust_Id", "cust_Username", "cust_PostalCode", etc.)
      >
      > * Also, associative tables use concatted table prefixes
      > ("custagt_Id", "custagt_RelStatusCode", etc.)
      >
      > * Also, there are many, many FKs. "Customer" is a fundamental
      > partition in our database; the majority of the tables have
      > a "tbl_CustId" field.
      >
      > * There are also many places where the standard "Cust" prefix is
      > embedded in other field names (e.g. tbl_BlahCustBlahId)
      >
      > * Then there is the matter of all the web/application source code ...
      >
      > BTW we use Sql Server 2005 with C# and ASP.NET (2.0).
      >
      > We can simply comb through and "fix" this. Honestly we could
      > probably get this done in about 2-3 solid days of continuous mind-
      > numbing work. It just hurts like hell and induces panic.
      >
      > BTW our best way of doing it is simple search-and-replace of the DB
      > scripts; is there a better way?
      >
      > If we're going to bite the bullet, now is the time to do it.
      >
      > Or, we could just always and forever tell developers: "We use the
      > word/abbreviation "Cust[omer] throughout our database and
      > application, but all the business users call them 'clients'. Just
      > always remember that: Customer means Client and Client means
      > Customer etc. etc. etc." That might not be so bad IF ONLY this were
      > the only case ...
      >
      > I would be glad to hear technical and non-technical solutions. Any
      > thoughts/opinions are welcome. This won't be the last time this
      > happens, of course. Am I missing something simple? Should we
      > definitely do it? Should we stop worrying and learn to live with it?
      > And so forth ...
      >
      > TIA, Rick McQ
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      > Yahoo! Groups Links
      >
      >
      >
      >
      >
      >
      >


      --

      C. Keith Ray
      <http://homepage.mac.com/keithray/blog/index.html>
      <http://homepage.mac.com/keithray/xpminifaq.html>
      <http://homepage.mac.com/keithray/resume2.html>
    • rickmcq10
      Scott, thanks for your reply. So far that s two for and zero against the refactoring, so I suppose we ll bite the bullet and do it. ... their ... and your
      Message 2 of 15 , Aug 2 4:26 PM
      • 0 Attachment
        Scott, thanks for your reply. So far that's two "for" and
        zero "against" the refactoring, so I suppose we'll bite the bullet
        and do it.

        In your reply you bring up two other interesting points:


        > > ... we have decided to call our customers "clients" to reflect
        their
        > > true nature to our company. The problem is that the standard
        > > abbreviation "Cust" is used extensively throughout our database.
        >
        > Yes. This is a classic example of coupling between your design
        and your
        > business domain. This sort of thing happens.

        Well, we have "customers" in the real world and we have a "tCust"
        table. Are you implying that there's a better name for that table?
        If so, I'd love to hear it. (BTW the abbrev of "Customer" to "Cust"
        has never been a problem, and has greatly simplified the system.)
        I'm not sure what "coupling" you're talking about.


        > >
        > > * Our standard table prefixes use "cust" (as
        > > in "cust_Id", "cust_Username", "cust_PostalCode", etc.)
        >
        > This is a questionable practice, as you're now discovering. You've
        > effectively coupled your column names to the table name, which in
        turn is
        > coupled to the business entity name. I would advise that you start
        > dropping the entity name from the column name. If the column
        UserName is
        > in the Customer table, I can pretty much guess that it must be the
        > customer user name we're talking about.
        >

        If that practice is questionable, IMHO the answer to the question
        is: "Definitely do it!"

        Why? Because _it_eases_refactoring_.

        Years ago I came to the realization that database columns are part
        of the "global namespace", and must be uniquely named. That way, any
        time you're refactoring code related to that specific real world
        attribute, you can always search and find _only_ the code dealing
        with that specific attribute. For example, our tCust and tAgent
        tables have (respectively) cust_Id and agt_Id fields,
        cust_StatusCode and agt_StatusCode fields, cust_TimeCreated and
        agt_TimeCreated fields, etc. etc. This allows us to use standard
        field naming (i.e. standard entity patterns) wherever possible, but
        still have uniquely named fields.

        Believe me, even if we have to do this heinous "Customer to Client"
        renaming project now, it will be nothing compared to the refactoring
        benefits we've already gained using this attribute naming practice.

        But that being said, I'm always open to new ideas...

        ---

        Again, thanks for your reply.


        Rick McQ
      • rickmcq10
        Just wanted to say thanks for your reply. I value your opinion on this. Rick McQ
        Message 3 of 15 , Aug 2 4:28 PM
        • 0 Attachment
          Just wanted to say "thanks" for your reply. I value your opinion on
          this.

          Rick McQ
        • Curt Sampson
          ... I probably wouldn t abbreviate that, but so long as the team is ok with it, it s fine either way. However, I would certainly not start table names with
          Message 4 of 15 , Aug 3 1:24 AM
          • 0 Attachment
            On Wed, 2 Aug 2006, rickmcq10 wrote:

            > Well, we have "customers" in the real world and we have a "tCust"
            > table. Are you implying that there's a better name for that table?
            > If so, I'd love to hear it.

            I probably wouldn't abbreviate that, but so long as the team is ok with
            it, it's fine either way.

            However, I would certainly not start table names with 't'. Everybody's
            going to know from the context that it's a table or view, so you
            don't need it for that reason, and typing it as a table, if this is a
            Hungarian notation kind of thing, inhibits refactoring, since you have
            to rename it if you convert it to a view.

            >>> * Our standard table prefixes use "cust" (as
            >>> in "cust_Id", "cust_Username", "cust_PostalCode", etc.)
            >>
            >> This is a questionable practice, as you're now discovering....
            >
            > ..._it_eases_refactoring_.
            >
            > Years ago I came to the realization that database columns are part
            > of the "global namespace", and must be uniquely named. That way, any
            > time you're refactoring code related to that specific real world
            > attribute, you can always search and find _only_ the code dealing
            > with that specific attribute.

            If you want to do that, why not just prepend the table name to all uses
            of the attribute: instead of "cust_UserName," try "cust.UserName".

            cjs
            --
            Curt Sampson <cjs@...> +81 90 7737 2974
            The power of accurate observation is commonly called cynicism
            by those who have not got it. --George Bernard Shaw
          • Scott W. Ambler
            ... I d call it Client in this case. The coupling is with the business name. You ve changed the business name, and now you re thinking about changing the
            Message 5 of 15 , Aug 3 2:26 AM
            • 0 Attachment
              On Wed, August 2, 2006 7:26 pm, rickmcq10 said:
              > Scott, thanks for your reply. So far that's two "for" and
              > zero "against" the refactoring, so I suppose we'll bite the bullet
              > and do it.
              >
              > In your reply you bring up two other interesting points:
              >
              >
              >> > ... we have decided to call our customers "clients" to reflect
              > their
              >> > true nature to our company. The problem is that the standard
              >> > abbreviation "Cust" is used extensively throughout our database.
              >>
              >> Yes. This is a classic example of coupling between your design
              > and your
              >> business domain. This sort of thing happens.
              >
              > Well, we have "customers" in the real world and we have a "tCust"
              > table. Are you implying that there's a better name for that table?
              > If so, I'd love to hear it. (BTW the abbrev of "Customer" to "Cust"
              > has never been a problem, and has greatly simplified the system.)
              > I'm not sure what "coupling" you're talking about.

              I'd call it Client in this case.

              The coupling is with the business name. You've changed the business name,
              and now you're thinking about changing the table name as a result.

              <snip>
              >
              >
              > Years ago I came to the realization that database columns are part
              > of the "global namespace", and must be uniquely named. That way, any
              > time you're refactoring code related to that specific real world
              > attribute, you can always search and find _only_ the code dealing
              > with that specific attribute. For example, our tCust and tAgent
              > tables have (respectively) cust_Id and agt_Id fields,
              > cust_StatusCode and agt_StatusCode fields, cust_TimeCreated and
              > agt_TimeCreated fields, etc. etc. This allows us to use standard
              > field naming (i.e. standard entity patterns) wherever possible, but
              > still have uniquely named fields.
              >
              > Believe me, even if we have to do this heinous "Customer to Client"
              > renaming project now, it will be nothing compared to the refactoring
              > benefits we've already gained using this attribute naming practice.

              If the practice works for you, then continue doing it. As long as you
              feel that the benefit outweighs the cost, then it's a good strategy for
              you.

              - Scott
              Practice Leader Agile Development, IBM Rational
              http://www.ambysoft.com/scottAmbler.html

              Refactoring Databases (
              http://www.ambysoft.com/books/refactoringDatabases.html ) is now
              available.
            • Steve Jorgensen
              ... I d put it more strongly than that. If you create a view to substitute for what was formerly a table, then you end up with a view called t ,
              Message 6 of 15 , Aug 3 9:20 PM
              • 0 Attachment
                Curt Sampson wrote:
                On Wed, 2 Aug 2006, rickmcq10 wrote:
                
                  
                Well, we have "customers" in the real world and we have a "tCust"
                table. Are you implying that there's a better name for that table?
                If so, I'd love to hear it.
                    
                I probably wouldn't abbreviate that, but so long as the team is ok with
                it, it's fine either way.
                
                However, I would certainly not start table names with 't'. Everybody's
                going to know from the context that it's a table or view, so you
                don't need it for that reason, and typing it as a table, if this is a
                Hungarian notation kind of thing, inhibits refactoring, since you have
                to rename it if you convert it to a view.
                
                  
                I'd put it more strongly than that.  If you create a view to substitute for what was formerly a table, then you end up with a view called t<something>, clearly indicating that it's a table, not a view.  I'd say that tables and views should simply be treated as record sources from the code's point of view.  The code doesn't want to know whether the source is a table or a view.

                - Steve J.
              • rickmcq10
                That puzzles me. I know that type prefixes are out of favor, but we use these type prefixes as an indispensible aid to refactoring, at both the database and
                Message 7 of 15 , Aug 4 8:32 AM
                • 0 Attachment
                  That puzzles me. I know that type prefixes are out of favor, but we
                  use these "type" prefixes as an indispensible aid to refactoring, at
                  both the database and application levels. Maybe that's because we
                  still predominantly use Search-and-Replace for refactoring in .NET,
                  but it's our only choice for most refactorings.

                  Here's the idea: A customer exists at several levels/forms in the
                  RDBMS-based application:

                  * tCustomer = the table that persists Customer attributes across
                  sessions
                  * vwCustomer = the "main" view that de-normalizes the tCustomer
                  information (by joining with the relevant adjacent tables). There
                  may be more than one of these.
                  * dacCustomer = the data access class that provides basic CRUD
                  operations (a la Eric Evan's "Repository" pattern or Martin
                  Fowler's " Table Module" pattern)
                  * cCustomer = the domain-level class that provides true object-level
                  methods on a real-world Customer object.

                  Now we can easily search for and isolate only those code sections
                  that are operating on a Customer _at_a_specific_level of the
                  database/application, or at_any/all_levels_ (by just searching
                  for "Customer" with no prefix).

                  These comments puzzle me:

                  * "Everybody's going to know from the context that it's a table or
                  view". True, but the problem is that it often takes time to deduce
                  from the context, whereas you know instantly when using a type
                  prefix. Even if the time to deduce is only several seconds, it adds
                  up when trying to read code fast. And sometimes, like with code
                  snippets, you don't even have context info. Maybe it's because I'm
                  an old-timer, but I love type clarification using prefixes.

                  * "you have to rename it if you convert it to a view": I can't think
                  of the last time we converted a table to a view, or why we would do
                  that. I'll read Scott's book. But the database would not require you
                  to rename it. In fact, in legacy upgrades, we often do use "t-named-
                  views" so that we can migrate (refactor, that is) from a legacy data
                  model to a better data model, while still allowing the old and new
                  applications to coexist. But that's a whole nother story …

                  * "If you want to do that, why not just prepend the table name to
                  all uses of the attribute: instead of "cust_UserName,"
                  try "cust.UserName"." Okay, but that's the same thing, isn't it?
                  And I know how to use underscores in field and variable names but
                  not dots, at least that's the case in C#.

                  Thx for all your feedback.

                  Rick McQ
                • Sammy Larbi
                  ... Yeah, I d have to side with Curt here. Were you implying there is no better name that tCust? Because certainly, Customer is better.
                  Message 8 of 15 , Aug 4 9:45 AM
                  • 0 Attachment
                    Curt Sampson wrote:
                    On Wed, 2 Aug 2006, rickmcq10 wrote:
                    
                      
                    Well, we have "customers" in the real world and we have a "tCust"
                    table. Are you implying that there's a better name for that table?
                    If so, I'd love to hear it.
                        
                    I probably wouldn't abbreviate that, but so long as the team is ok with
                    it, it's fine either way.
                    
                    However, I would certainly not start table names with 't'. Everybody's
                    going to know from the context that it's a table or view, so you
                    don't need it for that reason, and typing it as a table, if this is a
                    Hungarian notation kind of thing, inhibits refactoring, since you have
                    to rename it if you convert it to a view.
                    
                      
                    Yeah, I'd have to side with Curt here.  Were you implying there is no better name that tCust?  Because certainly, Customer is better.

                  • rickmcq10
                    I know that type prefixes are out of favor, but we use these type prefixes as an indispensible aid to refactoring, at both the database and application
                    Message 9 of 15 , Aug 4 8:24 PM
                    • 0 Attachment
                      I know that type prefixes are out of favor, but we use these "type"
                      prefixes as an indispensible aid to refactoring, at both the
                      database and application levels. Maybe that's because we still
                      predominantly use Search-and-Replace for refactoring in .NET, but
                      it's our only choice for most refactorings.

                      Here's the idea: A customer exists at several levels/forms in the
                      RDBMS-based application:

                      * tCustomer = the table that persists Customer attributes across
                      sessions
                      * vwCustomer = the "main" view that de-normalizes the tCustomer
                      information (by joining with the relevant adjacent tables). There
                      may be more than one of these.
                      * dacCustomer = the data access class that provides basic CRUD
                      operations (a la Eric Evan's "Repository" pattern or Martin
                      Fowler's " Table Module" pattern)
                      * cCustomer = the domain-level class that provides true object-level
                      methods on a real-world Customer object.

                      With these naming conventions we can easily search for and isolate
                      only those code sections that are operating on a Customer
                      _at_a_specific_level of the database/application, or
                      at_any/all_levels_ (by just searching for "Customer" with no prefix).

                      These comments puzzle me:

                      * "Everybody's going to know from the context that it's a table or
                      view". True, but the problem is that it often takes time to deduce
                      from the context, whereas you know instantly when using a type
                      prefix. Even if the time to deduce is only several seconds, it adds
                      up when trying to read code fast. And sometimes, like with code
                      snippets, you don't even have context info. Maybe it's because I'm
                      an old-timer, but I love type clarification using prefixes.

                      * "you have to rename it if you convert it to a view": I can't think
                      of the last time we converted a table to a view, or why we would do
                      that. I'll read Scott's book. But the database would not require you
                      to rename it. In fact, in legacy upgrades, we often do use "t-named-
                      views" so that we can migrate (refactor, that is) from a legacy data
                      model to a better data model, while still allowing the old and new
                      applications to coexist. But that's a whole nother story …

                      * "If you want to do that, why not just prepend the table name to
                      all uses of the attribute: instead of "cust_UserName,"
                      try "cust.UserName"." Okay, but that's the same thing, isn't it?
                      And I know how to use underscores in field and variable names but
                      not dots, at least that's the case in C#.

                      I like our naming conventions precisely because they help us to
                      continuously deliver and refactor. But perhaps I'm missing
                      something...

                      Thx for all your feedback.

                      Rick McQ
                    • Curt Sampson
                      Rick, Given your tCustomer/vwCustomer/dacCustomer/cCustomer explanation, your reasons for using the prefixes seem a bit stronger. But even there, I can see a
                      Message 10 of 15 , Aug 6 11:03 PM
                      • 0 Attachment
                        Rick,

                        Given your tCustomer/vwCustomer/dacCustomer/cCustomer explanation, your
                        reasons for using the prefixes seem a bit stronger. But even there,
                        I can see a lot of opportunities for refactoring your system into
                        something simpler and more automated, though I won't get into them right
                        now.

                        But to respond to a few particulars:

                        > * "Everybody's going to know from the context that it's a table or
                        > view". True, but the problem is that it often takes time to deduce
                        > from the context, whereas you know instantly when using a type
                        > prefix. Even if the time to deduce is only several seconds, it adds
                        > up when trying to read code fast. And sometimes, like with code
                        > snippets, you don't even have context info. Maybe it's because I'm
                        > an old-timer, but I love type clarification using prefixes.

                        The main point is that, with OO programming, you often don't *want*
                        to know the "type". So long as the object is talking to you using the
                        correct protocol, you don't care what it's actually doing underneath
                        the hood. Whether something you're SELECTing from is a table or a view
                        is generally irrelevant; all you care about at the level of "SELECT ...
                        FROM ..." is that you get back the appropriate data; table vs. view is
                        extra information that will only get in the way.

                        > * "you have to rename it if you convert it to a view": I can't think
                        > of the last time we converted a table to a view, or why we would do
                        > that.

                        Generally, to preserve backward compatability when doing database
                        refactorings. The other day I split a "user" table into two parts, one
                        to hold essential information that all users have, and another to hold
                        information about "registered" users that didn't apply to unregistered
                        users. The old users table was named "tblUsers" (not by me), and now
                        we have a tblUsers view that provides the same interface, which saved
                        changing a bunch of legacy code that's scheduled for deletion at some
                        point anyway.

                        > * "If you want to do that, why not just prepend the table name to
                        > all uses of the attribute: instead of "cust_UserName,"
                        > try "cust.UserName"." Okay, but that's the same thing, isn't it?

                        Sure, except that if you rename the table, you need not rename all of
                        the columns.

                        cjs
                        --
                        Curt Sampson <cjs@...> +81 90 7737 2974
                        The power of accurate observation is commonly called cynicism
                        by those who have not got it. --George Bernard Shaw
                      • Dave Sanders
                        ... Playing Devil s Advocate, as I can see this issue both ways: You say it saves a second or two when reading the code - how many minutes does it take to
                        Message 11 of 15 , Aug 7 5:48 AM
                        • 0 Attachment
                          > * "Everybody's going to know from the context that it's a table or
                          > view". True, but the problem is that it often takes time to deduce
                          > from the context, whereas you know instantly when using a type
                          > prefix. Even if the time to deduce is only several seconds, it adds
                          > up when trying to read code fast. And sometimes, like with code
                          > snippets, you don't even have context info. Maybe it's because I'm
                          > an old-timer, but I love type clarification using prefixes.

                          Playing Devil's Advocate, as I can see this issue both ways:

                          You say it saves a second or two when reading the code - how many
                          minutes does it take to review code / beat on developers because they
                          don't follow the proper conventions? If you don't have to do this,
                          then GREAT - but I've never been in a shop where we didn't spend time
                          trying to herd cats around naming conventions...

                          You gotta have standards, otherwise you get all sorts of madness, but
                          I've found that when the standards were shorter / smaller, they got
                          followed better, and overall everything ran smoother. But, to each
                          their own...

                          D
                        • Brandon Byars
                          ... We have one big Status table that I ve had my eye on for a long time now. It has a StatusType column that evidently was too unwieldy to use, so a bunch of
                          Message 12 of 15 , Aug 8 4:50 PM
                          • 0 Attachment
                            > * "you have to rename it if you convert it to a view": I can't think
                            > of the last time we converted a table to a view, or why we would do
                            > that. I'll read Scott's book. But the database would not require you
                            > to rename it. In fact, in legacy upgrades, we often do use "t-named-
                            > views" so that we can migrate (refactor, that is) from a legacy data
                            > model to a better data model, while still allowing the old and new
                            > applications to coexist. But that's a whole nother story …
                            >

                            We have one big Status table that I've had my eye on for a long time
                            now. It has a StatusType column that evidently was too unwieldy to use,
                            so a bunch of views were built up around it, with inconsistent naming
                            standards--OrderStatus, vw_AccountPaymentStatus, etc. Several
                            applications /and/ reports are coupled to the views, so any renaming
                            will be painful. However, the views have problems, particularly with
                            referential integrity. It would be cleaner, from a database perspective,
                            to have an OrderStatus table and use foreign keys in child tables. And
                            indeed, it would be quite simple to swap Status and OrderStatus, such
                            that Status is a view and OrderStatus a table, which should not affect
                            hardly any clients. It's the vw_AccountPaymentStatus' of the world that
                            make that more painful than it needs to be.
                            -Brandon Byars
                          Your message has been successfully submitted and would be delivered to recipients shortly.