SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Foreign key field naming conventions

    Im looking for a good naming convention for my foreign keys.

    For this example lets say we have two tables, StaffMember and NewsItem. StaffMember's can add NewsItems.

    What I have been doing is this:
    Code:
     +---------------------------+
     |StaffMember				|
     +---------------------------+
     |int	  StaffMemberID (PK)|
     |nvarchar Name			  |
     +---------------------------+
     
     +---------------------------+
     |NewsItem				   |
     +---------------------------+
     |int	  NewsItemID (PK)   |
     |int	  StaffMemberID (FK)|
     |nvarchar Name			  |
     +---------------------------+
    For the field in the NewsItem table that holds which StaffMember added the news item I've used the same field name as the primary key from the StaffMember table - StaffMemberID

    But where i come into the problem is this doesnt actually mean anything. This ID could be the StaffMember the news item is about (bad example i know). Or what if you wanted to store both these items (StaffMember that added the NewsItem and the StaffMember its about).

    My current solution doesnt work since one of the fields would have to break the convention (since i can't have two fields in the same table called the same name - obvisouly)

    So whats a better name for it that has meaning? Maybe this:
    Code:
     +---------------------------+
     |NewsItem				   |
     +---------------------------+
     |int	  NewsItemID (PK)   |
     |int	  AddedBy	   (FK)|
     |nvarchar Name			  |
     +---------------------------+
    But then, when you need to use the field in a query, whats a good alias for the data that comes from the join on this foreign key?
    Code:
     SELECT
     	n.NewsItemID
     ,	n.AddedBy
     ,	n.Name
     ,	s.Firstname AS AddedByFirstname
     ,	s.Lastname AS AddedByLastname
     LEFT JOIN
     	StaffMember s ON n.AddedBy = s.StaffMemberID
     WHERE
     	n.NewsItemID = 1
    Any help is appreciated.

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Imminent
    What I have been doing is this:
    [code]
    +---------------------------+
    |StaffMember |
    +---------------------------+
    |int StaffMemberID (PK)|
    |nvarchar Name |
    +---------------------------+

    +---------------------------+
    |NewsItem |
    +---------------------------+
    |int NewsItemID (PK) |
    |int StaffMemberID (FK)|
    |nvarchar Name |
    +---------------------------+
    That's fine!

    It is a good idea to use the name of the PK as the name of the FK, then it is instantly obvious where you are referencing from. The FK column has no meaning, it is just one way that RDBMS systems must work in order to define the relationships between tables. You have used the posted attribute method here, (which almost insists that one use the convention you chose) you have posted the StaffMemberID attribute to the NewsItem table. You could also have used "relation for relationship" in which you create a new table:

    NewsPosters
    NewsItemID (PK)
    StaffMemberID (PK)

    with the FKs actually being the PKs of the base tables.
    Also MySQL uses a shortened join syntax:

    select * from NewsItem join StaffMember using StaffMemberID

    If you want to, you can (in many RDBMSs, noit MySQL) actually name the relationship you have created between two relations, I would call this one something like "createdby".

    It's a good idea to use all lower case, depending on your RDBMS and its ANSI settings the case may be important.

    HTH
    Richard

  3. #3
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by asterix
    It is a good idea to use the name of the PK as the name of the FK, then it is instantly obvious where you are referencing from. The FK column has no meaning, it is just one way that RDBMS systems must work in order to define the relationships between tables.
    Yeah, it works when you only have one FK from the same table but what if you have two foreign keys linking to the same primary key in a table.

    Like i used in my example above:

    StaffMembers and add news, A news item is about a staff member. So to model that you'd need to do:
    Code:
      +---------------------------+
      |NewsItem				   |
      +---------------------------+
      |int	  NewsItemID	(PK)|
      |int	  StaffMemberID (FK)| <- Holds which staff member added the news item
      |int	  StaffMemberID (FK)| <- Holds which staff member the news item is about
      |nvarchar Name			  |
      +---------------------------+
    But you can't do this because the field names have to be different. And if you only changed the slightly you wouldn't know which one held the id of the staffmember who added the news item and which field held who the news item is about...

    Get it?

    So I want a good naming schema that can handle single and multiple foreign keys.


    Quote Originally Posted by asterix
    It's a good idea to use all lower case, depending on your RDBMS and its ANSI settings the case may be important.
    I hate lowercase. Using MS SQL Server 2000. Microsoft used mixed case and so will I.
    Last edited by Imminent; Jun 22, 2004 at 14:58.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    personally i always use CamelCase

    call 'em whatever makes sense

    +---------------------------+
    |NewsItem |
    +---------------------------+
    |int NewsItemID (PK)|
    |int AddedByStaffID (FK)| <- Holds which staff member added the news item
    |int AboutStaffID (FK)| <- Holds which staff member the news item is about
    |nvarchar Name |
    +---------------------------+
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Imminent
    Code:
    +---------------------------+
    |NewsItem				 |
    +---------------------------+
    |int	 NewsItemID	(PK)|
    |int	 StaffMemberID (FK)| <- Holds which staff member added the news item
    |int	 StaffMemberID (FK)| <- Holds which staff member the news item is about
    |nvarchar Name			 |
    +---------------------------+
    Yeah, fair enough. You have to name them differently. You could just name them according to the relationship they represent, as Rudy shows.

    But in 98% of all cases it is likely that you will only post the PK once to a different table. I find it much simpler to just use the name of the PK as the FK for these cases for the reasons I said above.

    Anyway, there are no rules without exceptions, so the rule would be:
    "The names of FKs are the same as the original PK, unless that name is already in use for a table column." No rules without exceptions!

    - Richard

  6. #6
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Imminent
    I hate lowercase. Using MS SQL Server 2000. Microsoft used mixed case and so will I.
    Microsoft did not use mixed case when naming the SQL Server objects for precisely that reason, on a case sensitive server or connection your code will fail if you didn't use the correct case.

    CamelCase is easier to read, I agree too, but you also have to watch your case when specifying objects.

    - Richard

  7. #7
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by asterix
    Microsoft did not use mixed case when naming the SQL Server objects for precisely that reason, on a case sensitive server or connection your code will fail if you didn't use the correct case.
    When I use camel case for naming objects and fields I use Camel Case when programming against the database. Mixing the two wouldn't be a good idea but I don't see a problem if you stick to Camel Case everywhere.

    This is from a microsoft best-practices application:
    Code:
     CREATE TABLE [dbo].[IssueTracker_IssueComments] (
     	[CommentId] [int] IDENTITY (1, 1) NOT NULL ,
     	[IssueId] [int] NOT NULL ,
     	[Comment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     	[UserId] [int] NOT NULL ,
     	[DateCreated] [datetime] NOT NULL 
     ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    Camel Case


Bookmarks

Posting Permissions

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