Database Field Advice

[uniopp]

Hi,

I'm setting up a table of public (national) holidays in different countries. I guess each country will have between 10 and 25 dates to be entered. If my limited knowledge of good database planning is correct, it's not good practice to enter something like (mm/dd) - 01/15,02/25,03/04,04/21,05/23,06/18,07/03,08/30,etc. into one field. I think each date should be entered seperatly but that means the user will have to enter 10 to 25 records which I don't want.

Any suggestions on how to handle this kind of problem?

Thank you.

Simon.

 

[r937]

do not think of it as "the user will have to enter 10 to 25 records"

think of it as "the user has to enter 10 to 25 dates"

this will not change no matter how you store them

do not store them in one field, you are only asking for trouble


rudy

 

[uniopp]

If the dates are not going to be searched or manipulated and are only used as straight text would it still be a BAD idea to store them in one field????
What I mean is that all the dates for one country (or area) would be entered and only used as part of a country description. As far as I can see, there would never be a need for anyone to search or manipulate the dates. It would just be -
Country - Public Holidays
Canada - 01/01,03/21,05/12,06/30,10/25...etc.
Australia - 02/11,03/29,05/11,08/30,12/25...etc.
etc.

Would I still be breaking the MySQL Normalization rules.
Thank you.
Simon.

 

[r937]

yes you would still be breaking normalization rules (not just mysql's, any database's)

if there would never be any need to manipulate or search the dates, why are you doing this? who is it for? will it be public? how can you be so sure that you might not want to offer a function like "see if today is a holiday anywhere in the world?"

besides, what's wrong with storing them in normalized fashion? are you worried about storage space? how many countries are you going to cover?

have you never heard of never saying never? ;-)

 

[sleipnir214]

r937 is right.

If you're going to store all the dates for a country in a single record, you may as well store the dates in a text file. You'll just be wasting the power of any database engine.

And I cannot see how storage could ever be a problem. Even if you store 100 dates per country, there aren't that many countries -- the CIA World Fact Book has listings for 269 geopolitical entities. That works out to 26,900 holidays. MySQL can readily handle databases 10,000 times that size.
Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!