Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Nov 2003
    Posts
    29

    Unanswered: DB Design - alphabetical column names?

    I have been discussing with some coworkers whether or not it makes sense to invest the time to alphabetize the column names in our tables (aside from the PK and possibly FK's that could be listed first). My reasoning for doing so would make it much easier to scan the list of columns in a table that I was not familiar with to see if it contained a particular column (i.e. meeting_id). I was just wondering if this is common at all in our industry for new DB design (I realize why legacy systems would not be ordered in this way). I remember seeing MSFT designed their tables this way when Site Server first came out.

    An argument was made that when you add a new column you would insert it into the appropriate location and in order to make this happen, EP needs to create a temp table, move the data and consequently lock the table until the data has been transferred. While this is correct, I do not see this negative as outweighing the positive experience achieved by scanning a list of fields in a table in a more orderly fashion.

    Thoughts?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In the "true relational" model, rows and columns have no order within a table, only within a result set. The way that I find columns is to do a simple SELECT from the appropriate INFORMATION_SCHEMA view, which works nicely without any regard to column order.

    I wouldn't sweat the order of the columns. I don't see it being worth the overhead needed to support keeping any particular order, especially since that confuses people (especially developers) into thinking that rows and column should have an order before you SELECT them!

    -PatP

  3. #3
    Join Date
    Nov 2003
    Posts
    29
    I guess my point is, it is much easier to expand the field list in a table in QA then it is to write a query to give me an alphabetized list of columns within that table.

    Our DB needs a rework anyway, this would accomplish a few things in the process, namely identifying which procs are fetching data via SELECT * as well as those that are doing non-parametized INSERT's. I was just curious if this approach is common at all in new databases that are designed, or where a good source (link) would be regarding this, or if people just order the columns in their tables in any old order since the order does not matter.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I work hard to prevent folks from thinking that there IS any order before you create a result set, and that when you create the result set you can make ANY order that you want. While I once believed that column ordering within tables helped (for very much the same reason that you are using), now I'm very much in favor of columns being in whatever order they happen to hit the disk...

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't know...I like to place commonly used items first

    CREAT TABLE Employees(PersonId varchar(12), LastName varchar(30), FirstName varchar(15)...

    Then there's always the case for performance....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i'm with brett
    i typically use columns in the order that they are asked for

    first last mid address city st zip email etc.........

    However, what's the diff. alphabetize them if you want to, it wont hurt anything but it will only help satisfy your OCD.
    i would consider however that indexes, and other performance improvements would be much higher on my list.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    I don't know...I like to place commonly used items first

    CREAT TABLE Employees(PersonId varchar(12), LastName varchar(30), FirstName varchar(15)...

    Then there's always the case for performance....
    In result sets, I'm with you 100%. I like to point out that each user can create their own view with the same base name as the table, so that the column order can be customized any way they like. That also gets them thinking something like "crud, the second column is only second for me unless I explicitly list the columns in my result set", which has solved a number of my problems all by itself.

    I only change column orders from the default if there is some compelling reason to do so. I usually have the PK (either GUID or IDENTITY) first, since that's the first column defined, which also helps performance.

    -PatP

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not sure what you even mean by "Alphabatize" the columns? I hope to God you are not suggesting something like:

    A_PrimaryKey
    B_ContactName
    C_ContactPhone
    D_Address1
    E_Address2
    F_City
    G_State
    ....blah blah blah....

    If I saw that in a database I think I'd just fall on the floor laughing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95

    Talking

    And what happened when they got to Z_????

    My favourite quote applies here
    "And this was by design???"

  10. #10
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    you've seen excel
    a-z
    then
    aa ab ac ad ae
    all the way to IV

    that will give you 255 columns per table
    now, with a 1 to 1 relationship to another table you could get the sql limit of 1024 columns.

    seriously, i am under the impression that he just wants alphabetized columns of whatever names they already have.

    address
    birthdate
    city
    eMail
    firstname
    lastname
    state
    zip
    Last edited by Ruprect; 12-13-04 at 16:28.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, I get it. He just wants to rearrange the existing columns.

    I'd still get a chuckle out of seeing that in a database. I'd figure the developer was more anal retentive than sensible. Group associated columns together, please!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Placing columns in a "specific" order is actually a good exercise. But it should not be based on "usability", but rather on the data type. Internally SQL reorders your column list and puts the smallest/fixed datatypes first, and variable-length datatypes last (also from smallest to largest). So if your table contains the following:

    1 - id uniqueidentifier
    2 - date datetime
    3 - someflag char(1)

    then the server will restructure it in the following order:

    1(3) - someflag
    2(2) - date
    3(1) - id
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    you always have to come up with the logical reason for performing any action

    i feel dirty

  14. #14
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    crap
    ignore this
    it was a double post
    Last edited by Ruprect; 12-13-04 at 17:50.

  15. #15
    Join Date
    Nov 2003
    Posts
    29
    Quote Originally Posted by Ruprect
    you've seen excel
    a-z
    then
    aa ab ac ad ae
    all the way to IV

    that will give you 255 columns per table
    now, with a 1 to 1 relationship to another table you could get the sql limit of 1024 columns.

    seriously, i am under the impression that he just wants alphabetized columns of whatever names they already have.

    address
    birthdate
    city
    eMail
    firstname
    lastname
    state
    zip
    Yes this is exactly what I am talking about. I guess no one has worked with Site Server, I am not sure if Microsoft has carried over this philosophy to the latest versions of Commerce Server, can anyone confirm?

    The reason is not OCD or anal retentive related. Let's say you know you need to join to 5 different tables, a common field we use here is ACTIVE_IND so that we can perform soft deletes when necessary. Can't you see the value in just drilling down on the tables in the object browser in QA to see if the table has an ACTIVE_IND field or not? With tables of 40+ fields it will give you a headache (a migraine through repetition) of having to scan the entire list of fields to see if the table has an ACTIVE_IND field that needs to be checked. Sure over time you begin to memorize which tables you need to check, but for the new employee it will just add to their stress and learning curve.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •