Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Possible Newbie type question but.....

    If I have the Job Number linking various tables, can I call the field JobNum in each table

    tblOriginal.JobNum
    tblDelDockets.JobNum
    tblInvoices.JobNum
    tblItems.JobNum

    or should I have

    tblOriginal.JobNum
    tblDelDockets.DJobNum
    tblInvoices.VJobNum
    tblItems.IJobNum

    I am trying clean up code and I had

    strSQL = "SELECT * FROM tblOriginal WHERE [Job #]=" & [Job#]< job number from form from underlying table tblDelDockets

    and now have

    strSQL = "SELECT * FROM tblOriginal WHERE [JobNum]=" & [JobNum] I changed all variations of fieldname Job* to JobNum using the FIND AND REPLACE addin by Rick Fisher

    Now when I run the code it tells me that the 2nd [JobNum] is empty but it if I refer to the textbox name txtJobNo it works.

    Have I done a bad thing?
    Last edited by weyrman; 2015-05-14 at 02:31.
    "Heading for the deep end"

  2. #2
    jwoods
    Guest
    You can just call it JobNum in all the tables, although some prefer the abbreviated table name as part of the column name, similar to your example.

    Table aliasing in a query or stored procedure sort of kills that notion, as you see in this example...

    SELECT a.CompanyName, b.OrderID, c.LastName
    FROM Northwind..Customers a
    INNER JOIN Northwind..Orders b ON a.CustomerID = b.CustomerID
    INNER JOIN Northwind..Employees c ON b.EmployeeID = c.EmployeeID

    Using a, b, and c does the same thing.
    Last edited by jwoods; 2015-05-14 at 02:55.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    561
    Thanks
    51
    Thanked 68 Times in 66 Posts
    The field can have the same name in every table. SQL won't let you create ambiguous references, no matter what you do.

    As jwoods said, table aliases are typically used to distinguish which JobNum field you are referring to. Even in the absence of aliases though, you can always qualify the field with the table, just as you did in your example. Thus,

    tblOriginal.JobNum
    tblDelDockets.JobNum
    tblInvoices.JobNum
    tblItems.JobNum

    These are perfectly valid field names, and SQL will know exactly what you are talking about.

    AFAIK Codd never said anything about the rules for naming foreign keys (that's what these are BTW). In production systems it's not uncommon to see different names on the foreign key fields. However I don't much care for various names for the foreign keys. Though if it were to be done, your way is a decent way to implement.

    Here's the larger issue. Take someone new to your system, and they have to make a report, or figure out how it works at the schema level. If the foreign key fields have the same name as the primary key in it's home table, that's a huge hint that those fields relate to each other. Conversely, the more the field names diverge, the more difficult it is for someone to understand that there is that connection.

    Nor is this a theoretical problem. This issue arises regularly in real-world systems. For reporting in particular, relatively non-technical users get exposed to your schema every day. So take pity on them and keep the field names the same!

Posting Permissions

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