Field naming question

Question

How should the naming go for colunms used systemwide. For example city. In city lookup table the colunm is called city_id, city_name. Now city is used in other places like events - I have called it event_city which refers back to city_name. Then in user profile there is user_city which again does the same. Should these all be called the same or different as per the table even though they are all the same thing?

Answer 1

Exactly what your naming convention is is less important than that you stick to it, but I wouldn't recommend naming your 'city name' field on the event table event_city. Instead I'd suggest using city_name, because it's clearer. It's obvious that it's the event's city name, because it's on the event table.

Answer 2

You should rather use a foreign key reference to the Cities table referencing the city_id.

No need to change the name in other tables, just stick to the city_id foreign key.

Answer 3

From the Rubaiyat of Omar Khayyam:

Myself when young did eagerly frequent
Doctor and Saint, and heard great Argument
About it and about; but evermore
Came out by the same Door as in I went.

There are lots of discussions about how to do this and seldom much agreement. Choose a plausible scheme and stick with it - consistency is probably most important.

I would probably go with:

• City(Name, ...)
• Events(..., City, ...)
• UserProfile(..., City, ...)

I would only use a qualified name if there were two columns related to City in a single table:

• UserDetails(..., Home_City, ..., Work_City, ...)

As an aside, I dropped the 'City ID' column since you seemed to be joining to the city name. However, you might be better off with a City ID and having the other tables join to that, using CityID. This allows for the same city name to appear in multiple states, for a US-centric example.

Answer 4

I would change the event_city to city_id.

(I am assuming that event_city will be a reference to a city, and match the values in the city_id column. )