How if the naming choose colunms used systemwide. For instance city. In city research table the colunm is known as city_id, city_title. Now city can be used elsewhere like occasions - I've known as it event_city which refers to city_title. Then in account there's user_city which again does exactly the same. Should all of these be known as exactly the same or different according to the table despite the fact that they're the same factor?

You need to rather make use of a foreign key mention of Metropolitan areas table referencing the town_id.

You don't need to alter the title in other tables, just stay with the townidentification foreign key.

Precisely what your naming convention it's less important than that you simply stay with it, however i wouldn't recommend naming your 'city name' area around the event table event_city. Rather I'd suggest using city_title, since it is clearer. It's apparent it's the event's city title, since it is around the event table.

In the Rubaiyat of Omar Khayyam:

Myself when youthful did eagerly frequent
Physician and Saint, and heard great Argument
About this contributing to but evermore
Arrived on the scene through the same Door as with I went.

You will find plenty of discussions concerning how to do that and rarely much agreement. Select a plausible plan and stick to it - consistency is most likely most significant.

I'd most likely opt for:

  • City(Title, ...)
  • Occasions(..., City, ...)
  • UserProfile(..., City, ...)

I'd just use a professional title if there have been two posts associated with City in one table:

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

Being an aside, I dropped the 'City ID' column because you appeared to become joining towards the city title. However, you may be best having a City ID and getting another tables join to that particular, using CityID. This enables for the similar city title to look in multiple states, for any US-centric example.

I'd alter the event_city to city_id.

(I'm presuming that event_city is a mention of the a town, and match the values within the city_id column. )