Member Avatar for guif

Hi! I need help to design an structure and the relationship.
I need to create 4 tables: REGION, SUBREGION, CITIES and COUNTRY and do the relationships

I have this:

Table: REGION

CREATE TABLE REGION(
	REGION	VARCHAR(2),
	REGION_NAME VARCHAR(200),
);

Values:

REGION REGION_NAME
1 Africa
2 Americas


Table: SUBREGION

CREATE TABLE SUBREGION(
	SUBREGION VARCHAR(2),
	SUBREGION_NAME VARCHAR(200),
);

Values:

SUBREGION SUBREGION_NAME
1A Central Africa
1B Eastern Africa


Table: CITIES

CREATE TABLE CITIES(
	CC_FIPS VARCHAR(2),
	FULL_NAME_ND VARCHAR(200),
);

CC_FIPS FULL_NAME_ND
AA Angochi
AA Arasji


Values:

CREATE TABLE COUNTRIES(
	CC_FIPS VARCHAR(2),
	COUNTRY_NAME VARCHAR(100),
);

Table: COUNTRY

CREATE TABLE CITIES(
	CC_FIPS VARCHAR(2),
	FULL_NAME_ND VARCHAR(200),
	INDEX(CC_FIPS),
	INDEX(FULL_NAME_ND),
	INDEX(FULL_NAME_ND,CC_FIPS)
);

Values:

CC_FIPS COUNTRY_NAME
AA Aruba
AC Antigua and Barbuda
AE United Arab Emirates


Anybody help me with the relationships??
thanks!

  • 3 Contributors
  • 2 Replies
  • 131 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by swenning
Member Avatar for griswolf

For example, each city is in exactly one country, and each country has zero or more cities. Since this is always a one-to-many relationship, each smaller unit can have a column specifying the unique key of its next-larger unit. Similarly, each country is in exactly one sub-region, each sub-region in exactly one region, and there it stops.

Edited by griswolf because: add detail
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.