What would be the Better db design for the old db structure?

Question

i've a old database where i store the data of the holidays and dates in which they are celebrated..

id  country     hdate           description     link 
1   Afghanistan     2008-01-19  Ashura          ashura
2       Albania     2008-01-01  New Year Day    new-year

the flaws in the above structure is that, i repeat the data other than date for every festival and every year and every country..

For example, I store a new date for 2009 for ashura and afghanistan ..

I tried to limit the redundancy and split the tables as

countries (id,name)

holidays (id, holiday, celebrated_by, link) 
// celebrated_by will store the id's of countries separated by ','

holiday_dates (holiday_id, date, year) 
// date will the full date and year will be as 2008 or 2009

Now i have some problems with the structure too..

consider that i store the holiday like Independence day , its common for more countries but will have different dates. so how to handle this and and the link will have to be different too..

And i need to list the countries which celebrates the same holiday and also when i describe about a single holiday i need to list all the other holidays that country would be celebrating..

And the most of all , i already have huge amount of data in the old tables and i need to split it to the new one once the new design is finalized...

Any ideas?

Answer 1

What about having a n-n relationship?

Table: Country

• Country_ID (PK)

Table: CountryToHoliday

• Country_ID (FK)
• Holiday_ID (FK)

Table: Holiday

• Holiday_ID (PK)