var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: correct way to place data in table OneToONe
i am confused about the correct/most efficient way to place data in my dababase table when there is a OneToOne relationship.
for example. i have a users table .
i now wish for each user to be able to state his current country location.
i then want to be able to search the datatable for users by current location.
the way that i have done this is to create 3 separate tables. i.e
table one - users : just contains the user information:
Table two country list: a list of countries and respective Ids for each country
CREATE TABLE users(
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
firstName VARCHAR(30) NOT NULL,
lastName VARCHAR(40) NOT NULL,
Table 3; contains the userId and the countryId he lives in:
CREATE TABLE countrylist(
country_id MEDIUMINT UNSIGNED NOT NULL,
country VARCHAR(60) NOT NULL,
INDEX country_id ( country_id, country ),
INDEX countrylist (country, country_id ),
UNIQUE KEY (country)
CREATE TABLE user_countrylocation(
country_id VARCHAR(60) NOT NULL,
id MEDIUMINT UNSIGNED NOT NULL,
INDEX country_id (country_id, id ),
INDEX user_id (id, country_id )
Alternatively, should i place the countryId in the users table and completely get rid of the user_countrylocation. i.e in each user column, i will place a country_id for the country he lives in.
The problem is that i have over 20 similar tables as above that give details on users; i.e languages spoken, age-group, nationality etc
My concerns is that if i place this unique information in each users column in the user table, then what would be the most effident way to search the database: that is why i opted for the style above.
so, i really request for some advice on the most efficient/correct way to plan the database.
Thank you in advanced for your advise and assistance.
Tags for this Thread