Database desigining confusion {please help}

[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

[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).