I am trying to build a website that it will harvest user's info and store them into a table. Lets name the table user_details. It will have the columns listed below:
id,country,city,name,surname,birthday,gender,email,password,phone_number,search_phone_number
id = 1,2,3,4.. INT AUTO_INCREMENT NOT NULL UNSIGNED PRIMARY KEY phone_number= its unique but only in the same country. Maybe a person from UK will have the same phone_number with a person from Spain but not in UK. In other words its country-related. To make it unique the search_phone_number will be consisted of country+phone_number, ex: SPAIN394221234.
Users are going to search for other users using their unique search_phone_number.
Is there a need to try and normalize this table?
Also in the same website, i have built a simple PM system, and a gallery (showing the pictures the user uploaded). So i have 2 tables: 1)message table (sender, reiceiver,message,....) 2)photos table (directory, filename, ...)
Again, is there a need to normalize the message and photos tables?
I am a little bit confused, because in some occasions normalization is a must, but in some other its not.
Thanks in advance!
1 - create a new country table like ...
create table country (id int auto_incremenet primary key, country varchar(100));
2 - create a new city table like ...
create city (id int auto_increment primary key , city varchar(100), fk_country_id int);
fk_country_id will have the id of the country that city is located in
3 - in your table mentioned above, remove the country and city columns and add only fk_city_id column. fk_city_id will have the id of the city that person is located in
4 - create table for phones like
create table user_phones(id int auto_increment primary key, fk_country_id int, phone_number varchar(15)) ;
5 - add unique index to that table like so ..
CREATE UNIQUE INDEX idx_country_phone ON user_phones(fk_country_id, phone_number);
remove phone number and search_phone_number from your table and add fk_user_phones_id column to your table.. it will have the id value of relevant row from user_phones table
6 - when user is doing a search, filter by country id and and the phone number