How to simplify the following tables and optimize?

Question

Hi

Now i have a table with the following fields

id  
country     
hdate   
religions   
season  
description     
link    
rate

and i store the data within this table , my sample data will be

1  Afghanistan  2008-01-19 Islamic Winter  Ashura ashura 2
2  Afghanistan  2008-03-20 Islamic Winter Mouloud (Birth of the Prophet) mouloud 4
3  Afghanistan  2008-01-01 Common Winter  New Year Day  new-year    5
4  Albania  2008-04-28 Christian Spring Orthodox Easter Monday easter-monday 4
5  Albania  2008-01-01 Common Winter  New Year Day  new-year    5

here you can look there is data redundancy and so i've decided to break up the tables and bring some common sense.

I've created a draft but i couldn't finalize what could i do to bring a best data structure.

I decided to bring all countries in one table

and all the holidays data into one table (id, holiday, celebrated_by, religions season link rate) the celebrated_by stores the id of the countries seperated by comma

and the holiday_dates for storing the dates of the holiday. (holiday_id, date, year) date stores the complete date and the year stores only the year as 2009,2008 etc..

Now i want to list all the holidays of a particular country , list the countries which celebrates the same holiday (when a holiday is listed) etc..

Please suggest me whether this structure is enough or is there any flaws or any changes to be made.

help me

Answer 1

You should have two (or three) tables. one for countries and one for holidays for sure. I would suggest even adding a third for religion, if more than 1 religion share the same holiday. The latter can have a foreign key that links it to the countries table

countries
================
country_id  (auto increment)
name

religion
============
religion_id (auto increment)
name 

holidays
===============
holiday_id (auto increment)
date
season
description
link
rate
country_id (FK)
religion_id (FK)

Let me know if you need a hand with the joins to query.

Answer 2

if celebrated_by links with the country table's unique key, then it is good, u can easily handle this. But redundany would be there in holiday table

If not then u could either create one or can create another table which links b/w country and holiday (country_id, holiday_id) fields would be enough to sort out queries and reduce redundancy

Answer 3

hi and welcome to SO

the proposed structure is fine except that you shouldn't use comma-separated list of ids for linkage. Make 'celebrated_by' a separate table with fields country_id, holiday_id

Theoretically you might want to replace direct references to 'religion' or 'season' with reference tables (like pocketfullofcheese suggested), but I doubt this will be of any practical benefit.