Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. The Lounge
  3. Why do they insist on repeating the name of the table in the column name?

Why do they insist on repeating the name of the table in the column name?

Scheduled Pinned Locked Moved The Lounge
databasedesignquestion
84 Posts 32 Posters 11 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • G Gary Huck

    With regard to database design: Is it just me or are there others out there who are driven nuts by repeating the table name in the column name. E.g., I see things like Widget_Attribute_Type.Widget_Attribute_Type_Id all the time when all that is needed is Widget_Attribute_Type.Id. Seems when I debate this with the DBA types and architects they use the same [similar] tired arguments.

    L Offline
    L Offline
    Lost User
    wrote on last edited by
    #8

    Sometimes people do that to avoid the "Ambiguos column name error". I have really seen that. The problem is, once the column name has been there for some time, it's hard to change it.

    B 1 Reply Last reply
    0
    • G Gary Huck

      With regard to database design: Is it just me or are there others out there who are driven nuts by repeating the table name in the column name. E.g., I see things like Widget_Attribute_Type.Widget_Attribute_Type_Id all the time when all that is needed is Widget_Attribute_Type.Id. Seems when I debate this with the DBA types and architects they use the same [similar] tired arguments.

      C Offline
      C Offline
      Corporal Agarn
      wrote on last edited by
      #9

      Heck I have tables with "TABLE" as part of the table name. "Widget Attribute Table" Notice the spaces. Makes live interesting. The person who setup the original ACCESS database even used column names like "Widget Attribute Table Query #Last-Name", notice the pound sign and dash. However, I cannot complain too much as he is married to the company founder. :sigh:

      G J M 3 Replies Last reply
      0
      • T tgrt

        It makes joins clearer in regards to key fields, and I assume that's what you're talking about. Although I wouldn't use such an unwieldly name in the first place. For example, I'd likely use AttributeTypeId. That would then match nicely as a foreign key which you'd name similarly.

        L Offline
        L Offline
        Lost User
        wrote on last edited by
        #10

        tgrt wrote:

        It makes joins clearer in regards to key fields

        It doesn't; it clutters up the statement with redundant text.

        SELECT *
        FROM Employee e
        JOIN Department d ON d.Id = e.fk_Department

        versus

        SELECT *
        FROM Employee e
        JOIN Department d ON d.DepartmentId = e.Employee_fk_Department

        ..worse are the people who add the text "table" to a tablename. ..worse than that, tools that make it easy to model data; any idiot can make a list, and any idiot does. Solving the same kind of errors, over and over. "The same customer is recorded three times in the database, and someone needs to correct that." Bring me an MS-Access database, without any relationships defined, without primary keys, and with every field being a varchar (memo!).

        Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

        T W 2 Replies Last reply
        0
        • L Lost User

          tgrt wrote:

          It makes joins clearer in regards to key fields

          It doesn't; it clutters up the statement with redundant text.

          SELECT *
          FROM Employee e
          JOIN Department d ON d.Id = e.fk_Department

          versus

          SELECT *
          FROM Employee e
          JOIN Department d ON d.DepartmentId = e.Employee_fk_Department

          ..worse are the people who add the text "table" to a tablename. ..worse than that, tools that make it easy to model data; any idiot can make a list, and any idiot does. Solving the same kind of errors, over and over. "The same customer is recorded three times in the database, and someone needs to correct that." Bring me an MS-Access database, without any relationships defined, without primary keys, and with every field being a varchar (memo!).

          Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

          T Offline
          T Offline
          tgrt
          wrote on last edited by
          #11

          You're talking about something different. The second query would be:

          SELECT *
          FROM Employee e
          JOIN Department d ON d.DepartmentId = e.DepartmentId

          I'm leaving the asterisk for the sake of brevity.

          L K 2 Replies Last reply
          0
          • C Corporal Agarn

            Heck I have tables with "TABLE" as part of the table name. "Widget Attribute Table" Notice the spaces. Makes live interesting. The person who setup the original ACCESS database even used column names like "Widget Attribute Table Query #Last-Name", notice the pound sign and dash. However, I cannot complain too much as he is married to the company founder. :sigh:

            G Offline
            G Offline
            Gary Huck
            wrote on last edited by
            #12

            Ow. My all time favorite, was back in the days of VMS (DEC). Their RMS system was a great file system (data files all ended with ".DAT"). A youngster created a file named "DATA.DAT" ... you can guess the directory name :)

            1 Reply Last reply
            0
            • G Gary Huck

              With regard to database design: Is it just me or are there others out there who are driven nuts by repeating the table name in the column name. E.g., I see things like Widget_Attribute_Type.Widget_Attribute_Type_Id all the time when all that is needed is Widget_Attribute_Type.Id. Seems when I debate this with the DBA types and architects they use the same [similar] tired arguments.

              R Offline
              R Offline
              Rama Krishna Vavilala
              wrote on last edited by
              #13

              As people have said it makes joins easier and intuitive to figure out what goes where in multi-table joins. Also most reporting tools automatically figure out the related fields if you follow this pattern.

              N B 2 Replies Last reply
              0
              • L Lost User

                I hate it when they do that on objects: Customer.CustomerCatagory Customer.CustomerType Why not just Customer.Catagory? Why not just Customer.Type?

                J Offline
                J Offline
                Jorgen Andersson
                wrote on last edited by
                #14

                Making a database consistent is not only about normalization. One should also have the same name on the fields everywhere you use them. So CustomerID is called CustomerID in both the Customertable and the Ordertable. There must never be any uncertainties. It's also ISO-11179 compliant.

                People say nothing is impossible, but I do nothing every day.

                1 Reply Last reply
                0
                • G Gary Huck

                  With regard to database design: Is it just me or are there others out there who are driven nuts by repeating the table name in the column name. E.g., I see things like Widget_Attribute_Type.Widget_Attribute_Type_Id all the time when all that is needed is Widget_Attribute_Type.Id. Seems when I debate this with the DBA types and architects they use the same [similar] tired arguments.

                  J Offline
                  J Offline
                  Jorgen Andersson
                  wrote on last edited by
                  #15

                  Making a database consistent is not only about normalization. One should also have the same name on the fields everywhere you use them. So CustomerID is called CustomerID in both the Customers table and the Orders table. There must never be any uncertainties. It's also ISO-11179 compliant.

                  People say nothing is impossible, but I do nothing every day.

                  C P 2 Replies Last reply
                  0
                  • L Lost User

                    tgrt wrote:

                    It makes joins clearer in regards to key fields

                    It doesn't; it clutters up the statement with redundant text.

                    SELECT *
                    FROM Employee e
                    JOIN Department d ON d.Id = e.fk_Department

                    versus

                    SELECT *
                    FROM Employee e
                    JOIN Department d ON d.DepartmentId = e.Employee_fk_Department

                    ..worse are the people who add the text "table" to a tablename. ..worse than that, tools that make it easy to model data; any idiot can make a list, and any idiot does. Solving the same kind of errors, over and over. "The same customer is recorded three times in the database, and someone needs to correct that." Bring me an MS-Access database, without any relationships defined, without primary keys, and with every field being a varchar (memo!).

                    Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

                    W Offline
                    W Offline
                    Worried Brown Eyes
                    wrote on last edited by
                    #16

                    Eddy Vluggen wrote:

                    ..worse are the people who add the text "table" to a tablename

                    Sometimes coupled with each field starting fld_ I'm fairly certain I had to work with something like tbl_Customer.fld_CustomerId in Access back in the nineties. Regards, Stewart

                    L K 2 Replies Last reply
                    0
                    • T tgrt

                      You're talking about something different. The second query would be:

                      SELECT *
                      FROM Employee e
                      JOIN Department d ON d.DepartmentId = e.DepartmentId

                      I'm leaving the asterisk for the sake of brevity.

                      L Offline
                      L Offline
                      Lost User
                      wrote on last edited by
                      #17

                      How is that more clear than without the table-name?

                      SELECT *
                      FROM Employee e
                      JOIN Department d ON d.DepartmentId = e.DepartmentId

                      SELECT *
                      FROM Employee e
                      JOIN Department d ON d.Id = e.fk_Department

                      One does not repeat the name of the table where the fk originates from; it's very confusing to have a foreign key that always consists of a table-name and id if you have multiple references to the same table;

                      SELECT *
                      FROM Humans h
                      JOIN Human hf ON h.fk_father = h.Id
                      JOIN Human hm ON h.fk_mother = h.Id

                      It's also kinda easy to have each primary key named "Id", and it keeps it readable, even for large structures. The foreign key should have a descriptive name - not just a concatenation of the originating table with the constant "Id". Below is your version;

                      SELECT *
                      FROM Humans h
                      JOIN Human hf ON h.HumanId1 = h.HumanId
                      JOIN Human hm ON h.HumandId2 = h.HumanId

                      Enjoy :)

                      Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

                      B T 2 Replies Last reply
                      0
                      • C Corporal Agarn

                        Heck I have tables with "TABLE" as part of the table name. "Widget Attribute Table" Notice the spaces. Makes live interesting. The person who setup the original ACCESS database even used column names like "Widget Attribute Table Query #Last-Name", notice the pound sign and dash. However, I cannot complain too much as he is married to the company founder. :sigh:

                        J Offline
                        J Offline
                        Jorgen Andersson
                        wrote on last edited by
                        #18

                        To paraphrase Joe Celko: There are a lot of furniture makers out there.

                        People say nothing is impossible, but I do nothing every day.

                        1 Reply Last reply
                        0
                        • C Corporal Agarn

                          Heck I have tables with "TABLE" as part of the table name. "Widget Attribute Table" Notice the spaces. Makes live interesting. The person who setup the original ACCESS database even used column names like "Widget Attribute Table Query #Last-Name", notice the pound sign and dash. However, I cannot complain too much as he is married to the company founder. :sigh:

                          M Offline
                          M Offline
                          Matthew Faithfull
                          wrote on last edited by
                          #19

                          I'm sure there's a joke in there somewhere about breaking Cod's laws and ending up in an entity relationship with someone at the same table but perhaps not. Sorry too much :java: not enough :zzz:

                          "The secret of happiness is freedom, and the secret of freedom, courage." Thucydides (B.C. 460-400)

                          1 Reply Last reply
                          0
                          • W Worried Brown Eyes

                            Eddy Vluggen wrote:

                            ..worse are the people who add the text "table" to a tablename

                            Sometimes coupled with each field starting fld_ I'm fairly certain I had to work with something like tbl_Customer.fld_CustomerId in Access back in the nineties. Regards, Stewart

                            L Offline
                            L Offline
                            Lost User
                            wrote on last edited by
                            #20

                            Yeah, Access gets confused if you don't tell it which is the table and which the field. I imagine it would try to read the table "Customer" from the field "Customer" if it's not told using a nice prefix which kind of object were talking about :suss:

                            Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

                            1 Reply Last reply
                            0
                            • G Gary Huck

                              With regard to database design: Is it just me or are there others out there who are driven nuts by repeating the table name in the column name. E.g., I see things like Widget_Attribute_Type.Widget_Attribute_Type_Id all the time when all that is needed is Widget_Attribute_Type.Id. Seems when I debate this with the DBA types and architects they use the same [similar] tired arguments.

                              V Offline
                              V Offline
                              Vivi Chellappa
                              wrote on last edited by
                              #21

                              Because they are clueless retards.

                              G 1 Reply Last reply
                              0
                              • T tgrt

                                You're talking about something different. The second query would be:

                                SELECT *
                                FROM Employee e
                                JOIN Department d ON d.DepartmentId = e.DepartmentId

                                I'm leaving the asterisk for the sake of brevity.

                                K Offline
                                K Offline
                                Keith Barrow
                                wrote on last edited by
                                #22

                                As opposed to the obscure:

                                SELECT *
                                FROM Employee
                                JOIN Department ON Department.Id = DepartmentId

                                Assuming Employee has an ID field (if not, the department. is redundant) or even the following, which is totally unambiguous:

                                SELECT *
                                FROM Employee
                                JOIN Department ON Department.Id = Employee.DepartmentId

                                Sort of a cross between Lawrence of Arabia and Dilbert.[^]
                                -Or-
                                A Dead ringer for Kate Winslett[^]

                                1 Reply Last reply
                                0
                                • W Worried Brown Eyes

                                  Eddy Vluggen wrote:

                                  ..worse are the people who add the text "table" to a tablename

                                  Sometimes coupled with each field starting fld_ I'm fairly certain I had to work with something like tbl_Customer.fld_CustomerId in Access back in the nineties. Regards, Stewart

                                  K Offline
                                  K Offline
                                  Keith Barrow
                                  wrote on last edited by
                                  #23

                                  I think you are making the basic error of confusing Access with a database :)

                                  Sort of a cross between Lawrence of Arabia and Dilbert.[^]
                                  -Or-
                                  A Dead ringer for Kate Winslett[^]

                                  1 Reply Last reply
                                  0
                                  • G Gary Huck

                                    With regard to database design: Is it just me or are there others out there who are driven nuts by repeating the table name in the column name. E.g., I see things like Widget_Attribute_Type.Widget_Attribute_Type_Id all the time when all that is needed is Widget_Attribute_Type.Id. Seems when I debate this with the DBA types and architects they use the same [similar] tired arguments.

                                    L Offline
                                    L Offline
                                    Lost User
                                    wrote on last edited by
                                    #24

                                    Two things I hate in column names: 1: Underscores 2: Abbreviations #1: I hate underscores because Account_Number isn't any easier to read than AccountNumber. Also, they are used inconsistently and add unneccessary length to column names. It really is a habit that needs to stop immediately. #2: I hate abbreviations in column names because it only causes confusion and doesn't really save any time/effort. In fact, it adds to the time it takes to maintain a database. Clarity should win out over saving the time it takes to type a couple of letters. The beauty is when you combine #1 & #2 to create mass confusion that saves nothing. For example: Acct_No Accout_Num Acc_Nbr Acct_Num I've seen several variations on AccountNumber within the same database because each and every administrator has his/her own clever take on using underscores and abbreviations. They all know they are expressing the phrase 'AccountNumber' but each of them uses a variation on a ridiculous naming convention. Of course, one day I'll be an administrator so I'll add my own variations a few days before I retire: A_cct_No_mber Ac____nt_NUMber Ac_WTF?_Number -MehGerbil

                                    C B U 3 Replies Last reply
                                    0
                                    • E Ennis Ray Lynch Jr

                                      My personal preference:

                                      create table customer
                                      id,
                                      name,
                                      dateOfBirth, etc

                                      I have a real preference for 4th normal because I don't like null checks in code, The down side is a less natural object model. For foreign keys:

                                      create table order
                                      id,
                                      customerId,
                                      etc

                                      It is actually, kind of funny, my rationalization for the Id. Code commonality. As far as the DB is concerned consistent trumps any rationalization but when it comes to writing code, writing less code is better. If Id is always the key value there are a lot of interfaces and base classes that can be written to support that. (No, I don't use code generators) [Yes, I know they can save a lot of time; yet I have never missed a dead-line because of DAL code--I am just that good] My real and true db pet peeve, however, is people that Alias all table names. There are cases for aliasing, sub-query joins, multiple joins on the same table, name too long, but to alias just to save typing significantly reduces the readability of the query. Consider:

                                      select o.id,c.id, /*notice here one of the reasons some people use table name?*/,
                                      l.id,c.name, op.method from order o,customer c, lineItem l, orderPayemnt op
                                      where o.customterId=c.id and l.orderId=o.id and op.orderId=o.id

                                      vs:

                                      select
                                      order.id orderId,
                                      customer.id customerId,
                                      lineItem.id lineItemId,
                                      customer.name,
                                      orderPayment.method
                                      FROM
                                      customer
                                      JOIN order ON
                                      order.customerId = customer.Id
                                      JOIN lineItem ON
                                      lineItem.orderId = order.id
                                      JOIN orderPayment ON
                                      orderPayment.orderId = order.id

                                      With the expense of a few extra key strokes, every one and their mother can read and modify the query.

                                      Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost "All users always want Excel" --Ennis Lynch

                                      P Offline
                                      P Offline
                                      Phil J Pearson
                                      wrote on last edited by
                                      #25

                                      Why would you want people's mothers to modify the query?? You just spoiled a good argument! ;P

                                      Phil


                                      The opinions expressed in this post are not necessarily those of the author, especially if you find them impolite, inaccurate or inflammatory.

                                      P 1 Reply Last reply
                                      0
                                      • V Vivi Chellappa

                                        Because they are clueless retards.

                                        G Offline
                                        G Offline
                                        Gary Huck
                                        wrote on last edited by
                                        #26

                                        :)

                                        1 Reply Last reply
                                        0
                                        • L Lost User

                                          I hate it when they do that on objects: Customer.CustomerCatagory Customer.CustomerType Why not just Customer.Catagory? Why not just Customer.Type?

                                          P Offline
                                          P Offline
                                          Phil J Pearson
                                          wrote on last edited by
                                          #27

                                          MehGerbil wrote:

                                          Why not just Customer.Catagory?

                                          Because misspelled column names are even worse than overly long ones! ;P

                                          Phil


                                          The opinions expressed in this post are not necessarily those of the author, especially if you find them impolite, inaccurate or inflammatory.

                                          L 1 Reply Last reply
                                          0
                                          Reply
                                          • Reply as topic
                                          Log in to reply
                                          • Oldest to Newest
                                          • Newest to Oldest
                                          • Most Votes


                                          • Login

                                          • Don't have an account? Register

                                          • Login or register to search.
                                          • First post
                                            Last post
                                          0
                                          • Categories
                                          • Recent
                                          • Tags
                                          • Popular
                                          • World
                                          • Users
                                          • Groups