How do I normalize this case into database?

??

id, country, jan, feb, mar, apr ...

1, usa, 0, 0, 1, 1

It is similar to a many-to-many relationship where you might have a countries table and a months table related via a pivot table. This might be overkill if you did not wish to get the countries from a given month instance; in that case, you could instead define a hasMany relation on the Country model to a table which associates a country_id with a month number.

@newbie360 I don't think this is a good solution

@tykus I thought in many-to-many relationship, but I don't have a months table and I don't want to create it. I will wait to find another solution if it is possible.

because i can't guess what you trying to do ;)

any table link up to this table ?

Save it as a json object?

And maybe only the "TRUE" on the months so you save space by not saving the FALSE

Schema::create('country', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name'); // USA, UGA
            $table->timestamps();
        });

 Schema::create('month', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name'); // jan, feb
            $table->timestamps();
        });

//use hasMany relationship, you don't need to use the 'no' and 'yes'
//Attach the months would like to have a 'yes' to the country 
//and nonattached months will be the one with "no"

public function months()//In the country model class
{
    return $this->hasMany(App\Month::class);
}

//Yes months
$country->months

//No month
Month::whereNotIn($country->months->pluck('id'))->get()

@AbdallahSabri I gave you the option to use stating that a many-to-many would be overkill. Read my post again.

How do you need to use/search this data? Will you ever need to do a query like "give me all of the countries that are good to visit in September?"

Me approach will be create a preferred_months table, that only record the preferred months, like this...

Schema::create('preferred_months', function (Blueprint $table) {
    $table->integer('country_id')->unsigned();
    $table->integer('month');
        });

So when you need to know what are the months you only need to do $country->months.

Regards.

@Cronix Exactly, I need the way that answers this question "give me all of the countries that are good to visit in September?"

I would go with with @newbie360's first suggestion of having months as a columns of your table. Also I think it is the best normalised solution, let me explain why.

Contrary to what it looks like, its not a many to many relation. Its exactly 12 one to one relations for each month of the year with the allowed values like "good to visit", "not good to visit" and "uncertain".

You have two options to design your table for multiple months.

Table 1:

| id | country_id | jan | feb | ... |
|----|------------|-----|-----| ----|
| 1  | XXX        | 0   |  1  |     |

Table 2:

| id | country_id | month | good_to_visit |
|----|------------|-------|---------------|
| 1  | XXX        | jan   | 1             |
| 3  | XXX        | apr   | 1             |

The only benefit I see in using table 2 is that you can skip creating rows values when its not a good time to visit.

My reasons for preferring the option 1 are :

1. The number of months is fixed, unchanging.
2. The table 1 has very high readability.
3. You can keep track of values that are still unknown/uncertain by using a null value. If you do that in Table 2, the sole benefit of skipping rows is lost.
4. You don't have to create any additonal table for months or use enum values to enforce limits on allowed values.
5. You can update the entire row for a city in single query instead of making 12 queries. Also, if you are not saving values for all months in table 2, you have to issue delete statements while updating.
6. The query "give me all countries that are good to visit in september OR october" is simpler. Table 2 requires a distinct clause.
7. The query "give me all countries that are good to visit in september AND october" or "give me all countries that are good to visit in september but not in december" is quite complex in table 2.