Return to Revisions

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.