How to simplify the following tables and optimize?

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.

Note:
for example, the republic day is celebrated in many countries, so when i mention the things about republic day i want to mention the countries with that holiday. In turn i should be able to all the holidays for single country from the table itself and when i display the info abt a holiday i should be able to list all the countries that celebrate that particular holiday. So i want them to be very simple to fetch without much complexity. Thats my exact need

help me

Thats fine. i’ll do

I have changed the table little bit different so it has an id from the table
that relate holidays to countries instead of holiday id…

So if i need to get the current year’s holidays for USA, then i need to query like

select  holiday.name as holiday , 
holiday_dates.date as date 
where 
holiday.id in 
(select hid from holiday_countries 
where 
countryid = (select id from country where name='USA')
) 
and
holiday_dates.year= YEAR(NOW()) 
and 
holiday_dates.holidayid in 
(select hid from holiday_countries 
where 
countryid = (select id from country where name='USA')
) 

in my old structure i would just use ,


select name, date 
from holidays 
where 
country='USA' and YEAR(date)=YEAR(NOW) 

Very much easy na :smiley:

Tell me if i’m wrong…

I haven’t yet cleared the db for corrections. I left it for some time…

Can anyone suggest me any good design so that i could move on from there?

one table for the countries

one table for the holidays

and one table to relate holidays to countries (this allows a many-to-many relationship)

yes – those aren’t valid mysql dates, you will want to use DATE instead of VARCHAR

Thanks, with addition to that i have a table called as holiday_dates which stores the holiday dates for all the years…

like

holiday_id year    date
1               2009   12-01-2009
1               2010   13-01-2010
2               2009   05-05-2009
2               2010   13-05-2010

, is that right?

will there be problem with this?

yup, your query is missing the FROM clause :wink:

also, it’s ~way~ too complicated… try to rewrite it with joins :slight_smile:

any time you store a series of comma-separated values in a single column, you need to create a separate table for this relationship

If i have to store the countries id which are celebrating the holiday then should i store them in different table?

If so then the countries id will be repeated so many times…

Is that a good method?

I dont mind scraping this and creating a complete new db design… I would like to know what would be good design for this kind of requirement and that will be reducing redundancy

If you don’t have any information related to the country (other than the country name itself), then there’s no need for a separate country table. You’d still have to add a foreign key to that table in your holiday_countries table, so you might as well put the country name in there.
The same goes for ‘religions’. Why that ‘s’? Can holidays have more than 1 religion? If so, you’d have to make a holiday_religions table.

From the info you’ve given, I’d say you would need two tables:

holidays
id
hdate
religion
season
description
link
rate

holiday_countries
holiday_id
country