Return to Answer

Your mistake is that you are trying to include two entities (country and brand) in one table - this has created the monster that you've described above.

If I were you, I'd start with a design like this!

CREATE TABLE country
(
  country_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  country_name VARCHAR (50) NOT NULL
 
  -- other fields...
);

CREATE TABLE brand
(
  brand_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  brand_name VARCHAR (50) NOT NULL,
  brand_image VARCHAR (100) -- why is an image a VARCHAR (100)? file location?
  brand_created DATE NOT NULL,
  brand_enabled TINYINT(1)
);

CREATE TABLE country_brand
(
  cb_country_id SMALLINT NOT NULL REFERENCES country (country_id),
  cb_brand_id SMALLINT NOT NULL REFERENCES brand (brand_id),
  cb_brand_enabled TINYINT(1)  NOT NULL,
  CONSTRAINT cb_pk PRIMARY KEY (cb_country_id, cb_brand_id),
  CONSTRAINT cb_rev_ix INDEX (cb_brand_id, cb_country_id)
  -- I always like explicitly naming my PRIMARY KEYs - error messages are so much more meaningful!
);

Disclaimer: not sure of exact MySQL syntax - don't have a MySQL server running at the minute - i.e. untested!

The key here is a joining table in a many to many relationship - i.e. between countries and brands.

A response to the query about performance.

A table with ~ 750,000 records and 3 fields is way more preferable to one with 3000 records and 250 fields!

You can index country.country_name and also brand.brand_name - if your performance is poor - but I think it will be much better than your previous setup!

750,000 is tiny by today's standards - plus the amount of data is unchanged, it's just far better organised! With an index, searches will be in the millisecond range!

Thanks for @RickJames for his tips from here.

Your mistake is that you are trying to include two entities (country and brand) in one table - this has created the monster that you've described above.

If I were you, I'd start with a design like this!

CREATE TABLE country
(
  country_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  country_name VARCHAR (50) NOT NULL
 
  -- other fields...
);

CREATE TABLE brand
(
  brand_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  brand_name VARCHAR (50) NOT NULL,
  brand_image VARCHAR (100), -- why is an image a VARCHAR (100)? file location?
  brand_created DATE NOT NULL,
  brand_enabled TINYINT(1)
);

CREATE TABLE country_brand
(
  cb_country_id SMALLINT NOT NULL REFERENCES country (country_id),
  cb_brand_id SMALLINT NOT NULL REFERENCES brand (brand_id),
  cb_brand_enabled TINYINT(1)  NOT NULL,
  CONSTRAINT cb_pk PRIMARY KEY (cb_country_id, cb_brand_id),

  -- I always like explicitly naming my PRIMARY KEYs - error messages are so much more meaningful!
  UNIQUE INDEX cb_pk_rev_uq (cb_brand_id, cb_country_id)

  -- country_brand_primary_key_reverse_unique index
);

The key here is a joining table in a many to many relationship - i.e. between countries and brands.

A response to the query about performance.

A table with ~ 750,000 records and 3 fields is way more preferable to one with 3000 records and 250 fields!

You can index country.country_name and also brand.brand_name - if your performance is poor - but I think it will be much better than your previous setup!

750,000 is tiny by today's standards - plus the amount of data is unchanged, it's just far better organised! With an index, searches will be in the millisecond range!

Thanks for @RickJames for his tips from here.

Your mistake is that you are trying to include two entities (country and brand) in one table - this has created the monster that you've described above.

If I were you, I'd start with a design like this!

CREATE TABLE country
(
  country_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  country_name VARCHAR (50) NOT NULL
 
  -- other fields...
);

CREATE TABLE brand
(
  brand_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  brand_name VARCHAR (50) NOT NULL,
  brand_image VARCHAR (100) -- why is an image a VARCHAR (100)? file location?
  brand_created DATE NOT NULL,
  brand_enabled TINYINT(1)
);

CREATE TABLE country_brand
(
  country_brand_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  cb_country_id INTEGER NOT NULL REFERENCES country (country_id),
  cb_brand_id integer NOT NULL REFERENCES brand (brand_id),
  cb_brand_enabled TINYINT(1)  NOT NULL
);

CREATE UNIQUE INDEX cb_uq_ix on country_brand (country, brand) -- no duplicates.

Disclaimer: not sure of exact MySQL syntax - don't have a MySQL server running at the minute - i.e. untested!

The key here is a joining table in a many to many relationship - i.e. between countries and brands.

A response to the query about performance.

A table with ~ 750,000 records and 3 fields is way more preferable to one with 3000 records and 250 fields!

You can index country.country_name and also brand.brand_name - if your performance is poor - but I think it will be much better than your previous setup!

750,000 is tiny by today's standards - plus the amount of data is unchanged, it's just far better organised! With an index, searches will be in the millisecond range!

Your mistake is that you are trying to include two entities (country and brand) in one table - this has created the monster that you've described above.

If I were you, I'd start with a design like this!

CREATE TABLE country
(
  country_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  country_name VARCHAR (50) NOT NULL
 
  -- other fields...
);

CREATE TABLE brand
(
  brand_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  brand_name VARCHAR (50) NOT NULL,
  brand_image VARCHAR (100) -- why is an image a VARCHAR (100)? file location?
  brand_created DATE NOT NULL,
  brand_enabled TINYINT(1)
);

CREATE TABLE country_brand
(
  cb_country_id SMALLINT NOT NULL REFERENCES country (country_id),
  cb_brand_id SMALLINT NOT NULL REFERENCES brand (brand_id),
  cb_brand_enabled TINYINT(1)  NOT NULL,
  CONSTRAINT cb_pk PRIMARY KEY (cb_country_id, cb_brand_id),
  CONSTRAINT cb_rev_ix INDEX (cb_brand_id, cb_country_id)
  -- I always like explicitly naming my PRIMARY KEYs - error messages are so much more meaningful!
);

Disclaimer: not sure of exact MySQL syntax - don't have a MySQL server running at the minute - i.e. untested!

The key here is a joining table in a many to many relationship - i.e. between countries and brands.

A response to the query about performance.

A table with ~ 750,000 records and 3 fields is way more preferable to one with 3000 records and 250 fields!

You can index country.country_name and also brand.brand_name - if your performance is poor - but I think it will be much better than your previous setup!

750,000 is tiny by today's standards - plus the amount of data is unchanged, it's just far better organised! With an index, searches will be in the millisecond range!

Thanks for @RickJames for his tips from here.

Start with

CREATE TABLE country
(
  country_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  country_name VARCHAR (50) NOT NULL
 
  -- other fields...
);

CREATE TABLE brand
(
  brand_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  brand_name VARCHAR (50) NOT NULL,
  brand_image VARCHAR (100) -- why is an image a VARCHAR (100)? file location?
  brand_created DATE NOT NULL,
  brand_enabled TINYINT(1)
);

CREATE TABLE country_brand
(
  country_brand_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  cb_country_id INTEGER NOT NULL REFERENCES country (country_id),
  cb_brand_id integer NOT NULL REFERENCES brand (brand_id),
  cb_brand_enabled TINYINT(1)  NOT NULL
);

CREATE UNIQUE INDEX cb_uq_ix on country_brand (country, brand) -- no duplicates.

Disclaimer: not sure of exact MySQL syntax - don't have a MySQL server running at the minute - i.e. untested!

The key here is a joining table in a many to many relationship - i.e. between countries and brands.

Your mistake is that you are trying to include two entities (country and brand) in one table - this has created the monster that you've described above.

If I were you, I'd start with a design like this!

CREATE TABLE country
(
  country_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  country_name VARCHAR (50) NOT NULL
 
  -- other fields...
);

CREATE TABLE brand
(
  brand_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  brand_name VARCHAR (50) NOT NULL,
  brand_image VARCHAR (100) -- why is an image a VARCHAR (100)? file location?
  brand_created DATE NOT NULL,
  brand_enabled TINYINT(1)
);

CREATE TABLE country_brand
(
  country_brand_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  cb_country_id INTEGER NOT NULL REFERENCES country (country_id),
  cb_brand_id integer NOT NULL REFERENCES brand (brand_id),
  cb_brand_enabled TINYINT(1)  NOT NULL
);

CREATE UNIQUE INDEX cb_uq_ix on country_brand (country, brand) -- no duplicates.

Disclaimer: not sure of exact MySQL syntax - don't have a MySQL server running at the minute - i.e. untested!

The key here is a joining table in a many to many relationship - i.e. between countries and brands.

A response to the query about performance.

A table with ~ 750,000 records and 3 fields is way more preferable to one with 3000 records and 250 fields!

You can index country.country_name and also brand.brand_name - if your performance is poor - but I think it will be much better than your previous setup!

750,000 is tiny by today's standards - plus the amount of data is unchanged, it's just far better organised! With an index, searches will be in the millisecond range!