Paul Kendal Paul Kendal - 1 month ago 8
SQL Question

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:

CREATE TABLE users(
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
firstName VARCHAR(30) NOT NULL,
lastName VARCHAR(40) NOT NULL,
);


Table two country list: a list of countries and respective Ids for each country

PHP Code:
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)

);


Table 3; contains the userId and the countryId he lives in:

PHP Code:
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 efficient 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.

Answer

If you are going to have a huge data then you should keep the same approach and use the following method to keep the one to one constraint satisfied

if you don't have a huge data then you should keep the look up tables like country and use the reference for user in a column. but then you may need to allow them nulls that is make such optional information columns nullable.

The most efficient and exactly correct way is to first delete the data from the third table "user_countrylocation" for the user to be updated. Then insert the new location for the user. don't forget to use transaction.

your table 3 should have

country_id MEDIUMINT UNSIGNED NOT NULL,

instead of

country_id VARCHAR(60) NOT NULL,

and also change tyhe column name from id to user_id in all tables.

if you are using a stored procedure it would be like

create procedure sp_UpdateUserCurrentCountry (
                 @userID MEDIUMINT UNSIGNED,
                 @CountryID MEDIUMINT UNSIGNED)

begin 
as
   delete from user_countrylocation
   where user_id = @userID

insert into user_countrylocation
           (
           country_id,
           user_id
           )
 values
           (
           @CountryID,
           @userID
           )
END