Database desigining confusion {please help}

[slice]

Please suggest me best way of designing database in this situation.

There are only two database tables.
Table1 : Country
Table2 : Person

In table name Country there are only two fields
Field1 : ID
Field2 : Name {country name}

And problem is on designing table name Person...
I want to record countries every person has visited.
So two fields are confirm in my mind that are

Field1 : ID
Field2 : Name {person name}

but what is the best way to record persons who have visited multiple countries ?

If i put 3rd field as
Field3 : CountriesID

and record countries id for every country visited by every person then i will need to create multiple records like
suppose
person id = 3
person name = Jeff
countries he has visted are 3 with country codes as = 1,2,3

now i need to create three records in table name person as

3,Jeff,1
3,Jeff,2
3,Jeff,3

I am confused whether it would be right to create single record like this
3,Jeff,{1,2,3} ?

Or what is best way in this situation to design database for optimum results ?

Hopefully my question is clear to you

[shukla]

Add new table called person-country
add two fields as person-id and country-id
and save as many entries you want to save.
i think this is best solution for database design as it will be in normalized form

[slice]

In this way hundreds of new records would be created while if i adopt this

3,Jeff,{1,2,3}

Field3 : CountriesID [and then add multiple ids separated by comma]

it would reduce clutter and complete record of person would be in one row.

What you think about it ?

[shukla]

records will get reduced...
but if u want to retrieve it in one query that suppose country-id 2 is visited by which persons and so on.
i think no.of records will get increased but query time retrieval will get reduced.

[si_the_geek]

Using the style "{1,2,3}" may well mean fewer records, but each of those records will be larger (as the field will be char, rather than numeric), and as shukla said they will be much much harder to work with.

The style "Country1 / Country2 / ..." is even worse, as you will be using the space of all 255 fields for every single person, and the queries would be an absolute nightmare.

The method suggested is the way it is normally done, and I believe the way you should do it. The size of the data is much lower, and the queries are much easier to write (shukla is correct, they will run faster too).

[slice]

but how would you adjust when there are thousands of persons and hundreds of them have visited at least 100 places ?

[si_the_geek]

It doesn't matter how many people have visited how many countries.. the "PersonCountry" table will store all combinations, using one row per visit. If you want to add a country to the list of where a person has visited, simply add a new row; if you want to remove a country, simlpy delete the row.

The table would contain data like this (a re-arranged version of the data you posted above):

PersonCountry 
PersonID  CountryID
3            1
3            2
3            3

To find all of the countries that a person has visited, you would use an SQL statement like this:

SELECT Country.Name 
FROM Country
INNER JOIN PersonCountry ON (PersonCountry.CountryID = Country.ID)
WHERE PersonCountry.PersonID = 3

(replace 3 with the ID of the person)

[szlamany]

In this way hundreds of new records would be created while if i adopt this

3,Jeff,{1,2,3}

Field3 : CountriesID [and then add multiple ids separated by comma]

it would reduce clutter and complete record of person would be in one row.

What you think about it ?

I just have to comment on this...

This design you suggest is not a database design at all.

Database designs follow 1st, 2nd and 3rd normal form - which is paramount to making the data usable in SQL syntax.

The design you suggest is a flat design - it's fine if that's what you want.

But it is not and never will be a design that SQL queries can be written against.

btw - the PersonCountry table can have additional columns in it - like DateFirstVisited for example. It doesn't have to be just a "bridge table" - it can contain other information.

[slice]

It doesn't matter how many people have visited how many countries.. the "PersonCountry" table will store all combinations, using one row per visit. If you want to add a country to the list of where a person has visited, simply add a new row; if you want to remove a country, simlpy delete the row.

The table would contain data like this (a re-arranged version of the data you posted above):

PersonCountry 
PersonID  CountryID
3            1
3            2
3            3

To find all of the countries that a person has visited, you would use an SQL statement like this:

SELECT Country.Name 
FROM Country
INNER JOIN PersonCountry ON (PersonCountry.CountryID = Country.ID)
WHERE PersonCountry.PersonID = 3

(replace 3 with the ID of the person)

I understand it totally and its fine but wouldn;t it create heavy database and too many records ?
for 100 persons with 50 visits in this i have to create 5000 records while in {1,2,3...} method it would require only 100 records?

please enlighten me little more about this issue

[Merrion]

No - there should be no repeating groups in a database table...
You need a seperate table - countries visited (Person id, Country Id).

Have a look for the database nornalisation rules (Dr E. F. Codd) and follow them (to at least 3rd normal form).

[Hack]

Duplicate threads merged.

Please do not create duplicate threads in different forum sections which ask the same question.

Thanks.

[szlamany]

...i have to create 5000 records while in {1,2,3...} method it would require only 100 records?

please enlighten me little more about this issue

Databases are designed to store rows and process those rows

Physically the data is most likely stored in blocks together (if you are using MS SQL SERVER you can cluster the primary index and actually force the data to be in the same physical disk location.

But the most important reason to store separate rows is so you can find all the people in a country by saying:

WHERE COUNTRYCODE=x

If you store them all together in one big text field finding all the people who visited country x would require some kind of INSTR search logic - that's not the way SQL/database searches are designed to work.

5000 rows is not a lot. We have tables with 3, 4 and 5 million rows at our customer sites and the queries (written against intelligent indexes) process instantly.

[si_the_geek]

In addition to the speed and simplicity benefits that szlamany mentioned, I think we should also mention the size.

The number of rows is not the only factor in database size - there is the size of each row too. Based on your 100 people/50 visits example: In the {1,2,3...} case, you would have at least 140 bytes per row (I'd guess up to 200) for that information, so that would be a minimum total of 14000 (up to 20000) bytes. For the separate table you could store only 4 bytes per row, so that would be 20000 bytes in total.

As you can see the size in either case is not significant, and neither is the difference. As the database grows the size difference goes the other way (as the country codes will get longer), and the separate table becomes the smaller option.


The really big benefit tho is the efficiency - the speed of 'identical' queries for these methods will vary wildly. If a query for the {1,2,3...} style takes 30 seconds, I would expect the same query for the separate table to take under 1 second (and be much easier to write).

[slice]

thanks for clearing my doubts
i am using Microsoft Access ... is it powerful enough to handle thousands of records efficiently? (in case i use separate table)

Thank You

[si_the_geek]

It certainly is... I can't remember the size limits of an Access (either the official ones, or the actual 'safe' ones which are lower), but a database of this size is tiny compared to what it can handle.

The speed is fine too, and to be honest for something of this scale it will probably be faster than using a server database (as the network speed will become a much bigger factor than 'usual').