CODEWITHSUNDEEP
databasenaming
kino
kino

Reputation: 41

Field naming 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?

Upvotes: 1

Views: 101

Answers (4)

Jonathan Leffler
Jonathan Leffler

Reputation: 754010

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.

Upvotes: 2

Bingy
Bingy

Reputation: 644

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. )

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166406

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.

Upvotes: 3

Bennor McCarthy
Bennor McCarthy

Reputation: 11675

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.

Upvotes: 1

Related Questions