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!

Re: Help desiging mysql table tree structure 80 80

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.

Re: Help desiging mysql table tree structure 80 80

I would suggest RISE! Model your information and generate both database and application layer code for selected environments.

Read more:
http://en.wikipedia.org/wiki/RISE_Editor
or:
http://www.risetobloome.com

To download:
http://www.risetobloome.com/clickonce/riseeditor/riseeditor.htm

Be a part of the DaniWeb community

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