How to save countries to database

I am doing the script for a dating site.
In the form, there is an option for a person to choose what country they want their partner to be from. It is a simple solution if they just select one country, but how would i do it if they wanted their partner to be from 20 different countries. I can not save it to the normal table where all the other records are.

So if I save it to a whole new table, how would i do the query? :confused:

For example ...table 1 has age, sex, race
Table 2 has the list of countries

How would i do a search if user is looking for an 18 year old white female from USA or france or Britain or Brazil or Canada?

You need a MANY-TO-MANY table.

Thus:

Table: People
personid int auto_increment primary key
name
age
gender
homecountryid int references Countries(countryid)
... etc ...

Table: Countries
countryid int auto_increment primary key
countryname

Table: CountryPreferences
personid int references People(personid),
countryid int references Countries(countryid)



Do it this way, and every person can express a preference for any number of countries.

You could also make the 2 columns in CountryPreferences the primary key to avoid accidental, or otherwise, duplicate records.

Yes, re making the composite primary key. You'd be surprised how many people have no idea (a) that you can have a composite primary key or (b) how to specify one.

there's plenty of examples of setting PK constraints on the www

LOL! Sure, and there are plenty of examples of normalization and foreign keys and inner and outer joins and we could go on all day. Does that stop people from asking here, first, instead of doing a little research? Not last I checked.

not sure what your issue is.

All I said, and it wasn't directed at you personally but as just a general comment, was:

You could also make the 2 columns in CountryPreferences the primary key to avoid accidental, or otherwise, duplicate records.

If they want to ask here first, I don't have any problem with that :)

I think you are taking OP the wrong way Bullant. You're on the same page, different paragraphs.

I too think people should take the time to look up how stuff works, it is obvious many don't take the time.

Your point about the composite key was well received from what I can tell, he was just emphasizing the point that many people don't know you can do that.