CODEWITHSUNDEEP
phpmysqldatabase
user311509
user311509

Reputation: 2866

Should I Populate Country/Region Names at DB or Application Level?

I'm going through a tough decision (at least for me)...

My website would have a list of countries in dropdown list and list of cities based on chosen country. I decided to populate the list at db level as:

CREATE TABLE Country
(
    countryID - PK
    countryName - FK
);


CREATE TABLE City
(
    cityID   - PK
    countryID - FK
    cityName
);

CREATE TABLE Register
(
    registerID - PK
    cityID - FK

)

However, this can add some complexity in php back-end coding when inserting record into registration as well as retrieving record from countries/cities, because on registration form user will city name on droplist, user won't see cityID, so i will have to fetch cityID based on chosen cityName, etc. Therefore, i said why do i just put the countries and cities list at application level in fixed drop down list and make register table look as:

CREATE TABLE Register
(
    registerID - PK
    cityName
)

cityName gets inserted directly without us having to join or call multiple tables and get specific ID for a city so we can then grab the cityName, etc...

I will only normalize in crucial tables such as making relationships between Member and Post/Thread tables. A member can have multiple threads one-to-many relationship. Otherwise, things that relate to multi-value as countries list won't be considered at DB level for simplicity and rap sake.

What do you think? Advice ...

Upvotes: 0

Views: 622

Answers (3)

Lord Tydus
Lord Tydus

Reputation: 544

The nature of the data should drive the database model. Not your front end issues.

Why not load your cities into a drop down list? Set the display text=cityName. Set the value=cityID.

Upvotes: 1

SooDesuNe
SooDesuNe

Reputation: 10030

Not sure I understand the text of your question completely, but to answer the title of your question, I would keep country names in the database. Countries/Cities do change their names from time-to-time.

Upvotes: 1

programmer
programmer

Reputation: 918

I would suggest defining XML with the country name/id as the element and city names as the child elements. It makes it edit/read the xml data. I'm not sure whether you would like to consider this.

Upvotes: 0

Related Questions