3

I have a table of Teams. Each Team has an ID.
I have another table that holds Players. Each Player is assigned to a team.

So my tables might look something like

CREATE TABLE players (
   id        INTEGER   PRIMARY KEY
   name      STRING
   team_id   INTEGER   REFERENCES teams(id)
)

CREATE TABLE teams (
   id        INTEGER PRIMARY KEY
   name      STRING
)

Does writing _id make it any more clearer that the column is meant to hold an ID of a team (as opposed to maybe a team name)?

If I saw that team was linked to a key on the teams table I would assume the column is meant to hold an ID.

I find that if I started following a convetion where I tack on _id I'll end up with a lot of columns with _id in them, but am not sure whether it adds any value.

MxLDevs
  • 339
  • 1
  • 4
  • 10
  • 1
    This is one of those cases where is more important to be consistent than to be 'right' – Remus Rusanu Oct 22 '13 at 16:47
  • i feel like this is more individual preference and depends how frequently code is being visited by new dev. for me I do not prefer to use Column name PlayerID in Player. but PlayeID in table Team yes; that make sense. for example if there are 10 Attributes of Product in Product table I do not want every attribute prefix with "Product" word. of course these all attributes are for Product. but if any attribute is for Order in that Table I will prefer to use "Order" prefix for that Attribute. similarly adding _ID postfix "to indicate that this column is meant to hold Integers" doesn't sounds good – Anup Shah Oct 22 '13 at 18:01
  • @AnupShah ID does not mean integer. It means IDentification. – ypercubeᵀᴹ Oct 22 '13 at 18:02

2 Answers2

4

This is a matter of style, and likely to get religious. Personally, I do find it valuable to explicitly label ID fields TeamID and PlayerID; as you say, it makes it perfectly clear that the field stores an ID and not some other type of data, such as a label. Being explicit is usually more valuable than being brief, in the long term.

I prefer using PlayerID, TeamID, etc. over just ID, but offhand I can't give you a compelling reason. It means that your joins are consistently X to X (P.TeamID = T.TeamID), whereas P.ID and T.ID are very different fields which should not be joined. It's easier to confuse P.ID and T.ID when glancing through a query than P.PlayerID and T.TeamID. On the other hand, there's value in consistently using the same field name for your PK, too.

Jon of All Trades
  • 5,967
  • 5
  • 45
  • 62
0

You talked about naming the column in Players Team instead of Team_Id and that you would be easily able to tell that it is related to Team.Id. And yes, you can figure it out. And yes it's easy. If you have a foreign key to help you. And if you check the foreign keys before creating any queries. And if you don't have a foreign key and don't happen to have another column of the same data type in the Team table, say Team.TaxId or something along those lines. That's a lot of ifs to my mind.

One of the things you can do to make your code easier to read and more maintainable is to use consistent naming standards. That means that if you are going to reference Team.Id by a column Team make sure you do it consistently throughout your database. Players.Id will always tie to TableName.Players etc. If you do that and you document it then you shouldn't have any problems. The benefit to using Team.TeamId, Player.PlayerId, Player.TeamId etc is that it is a common naming standard and if you're company has a massive change over of DBAs the next group of people who are looking at your code will be more easily able to understand it.

Kenneth Fisher
  • 24,127
  • 12
  • 61
  • 114