Ayuda normalización tablas para ganar consistencia

[yoguuu]

Hola amig@s, les explico:

Tengo cierta ilusión en desarrollar una página de contactos. Pero a medida que voy trabajando en esto me van surgiendo dudas, sobretodo en el diseño de la base de datos.

La base de datos usa un engine InnoDB, con relaciones creadas en la misma instrucción de crear la tabla, con charset 'UTF8' y collation 'utf8_general_ci'.

1. Las entidades de esta base de datos son las siguientes:

1. Usuarios
2. Continentes
3. Paises
4. Comunidades autónomas
5. Ciudades autónomas
6. Provincias
7. Localidades
8. Orientaciones sexuales

De una forma muy clara podeis ver que es simple, son 'usuarios', 'zonas geograficas' y 'orientaciones sexuales'. Considero que en principio para una pagina de contactos simple (WEB 2.0 = Beta xD :P).

La cosa es que estas zonas geograficas... Continentes, paises, com... estas zonas, a medida que somos mas específico en diferentes partes del mundo reciben nombres diferentes (Comunidades autonomas / Estados), ( Provincias / Villas ) y así más...

Entonces, me pregunto si esta forma de hacer que he previsto en un futuro sería consistente... Por ejemplo si quiero comenzar haciendo solo una pagina de españa... y despues amplio a mas paises, y al hacerlo me encuentro que tal como lo estaba haciendo... para los demás paises no es posible así. Como puedo entonces abstraer este tipo de datos para no tener este tipo de problemas ? Quitando nombres y especificando niveles ? regiones de mayor nivel y menor nivel ? :S Cual es vuestra opinión de como deberia hacer...

Luego tengo solo 1 duda más: Un usuario tiene en la tabla asignada una zona geografica a través de las claves foraneas, pero puede darse el caso que un usuario, pueda pertenecer a una comunidad autonoma y no a una ciudad autonoma... saben ustedes si podria dar valores 'NULL' A uno de los dos valores sin que este valor 'NULL' afecte a la integridad referencial que las relaciones obligan ???

Que les parece de paso la normalización en este ejemplo ? alguna sugerencia ? GRACIAS DE ANTEMANO !!! y por su paciencia al leer este tocho...

Código:

@ = Entidad
PK = Clave primaria
FK = Clave foranea
& = Atributo
- = Datos

@ Usuarios
	PK Id
	FK Id continente
	FK Id país  
	FK Id comunidad autónoma
	FK Id ciudad autonoma /* No en el sql todavía, pero la sentencia se ejecuta correctamente y se crea la tabla */
	FK Id provincia
	FK Id localidad
	FK Id orientación sexual
	& Usuario
	& Contraseña
	& E-mail
	& Fecha de nacimiento
	& Estado del usuario
	& fecha cumplimiento estado
	
@ Continentes	
	PK Id
	& Continente
		- Africa			
		- América			
		- Asia			
		- Europa			
		- Oceanía

@ Paises
	PK Id
	& País

@ Comunidades autónomas
	PK Id
	& Comunidad autónoma

@ Provincias
	PK Id
	& Provincia

@ Localidades
	PK Id
	& Localidad

@ Orientaciones sexuales
	PK Id
	& Orientación sexual	
		- Heterosexual
		- Homosexual
		- Bisexual
		- Asexual
		- Pansexual


/* SQL DE LA BASE DE DATOS */

CREATE TABLE `Continentes` (
`id` INT( 1 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Continente` VARCHAR( 7 ) NOT NULL
) ENGINE = innodb CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `Paises` (
`id` INT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`País` VARCHAR( 50 ) NOT NULL ,
`Id continente` INT( 1 ) UNSIGNED NOT NULL ,
INDEX ( `Id continente` ) ,
FOREIGN KEY ( `Id continente` ) REFERENCES `Continentes` ( `id` ) ON UPDATE CASCADE ON DELETE CASCADE 
) ENGINE = innodb CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `Comunidades autónomas` (
`id` INT( 2 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Comunidad autónoma` VARCHAR( 50 ) NOT NULL ,
`Id país` INT( 3 ) UNSIGNED NOT NULL ,
INDEX ( `Id país` ) ,
FOREIGN KEY ( `Id país` ) REFERENCES `Paises` ( `id` ) ON UPDATE CASCADE ON DELETE CASCADE 
) ENGINE = innodb CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `Provincias` (
`id` INT( 2 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Provincia` VARCHAR( 50 ) NOT NULL ,
`Id comunidad autónoma` INT( 2 ) UNSIGNED NOT NULL ,
INDEX ( `Id comunidad autónoma` ) ,
FOREIGN KEY ( `Id comunidad autónoma` ) REFERENCES `Comunidades autónomas` ( `id` ) ON UPDATE CASCADE ON DELETE CASCADE 
) ENGINE = innodb CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `Localidades` (
`id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`localidad` VARCHAR( 50 ) NOT NULL ,
`Id provincia` Int( 2 ) UNSIGNED NOT NULL ,
INDEX ( `Id provincia` ) ,
FOREIGN KEY ( `Id provincia` ) REFERENCES `Provincias` ( `id` ) ON UPDATE CASCADE ON DELETE CASCADE 
) ENGINE = innodb CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `Orientaciones sexuales` (
`id` INT( 1 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Orientación sexual` VARCHAR( 50 ) NOT NULL
) ENGINE = innodb CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `Usuarios` (
`id` INT( 9 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`usuario` VARCHAR( 30 ) NOT NULL ,
`contraseña` VARCHAR( 30 ) NOT NULL ,
`email` VARCHAR( 100 ) NOT NULL ,
`fecha de nacimiento` DATETIME NOT NULL ,
`estado del usuario` VARCHAR( 20 ) NOT NULL ,
`fecha cumplimiento estado` DATETIME NOT NULL ,
`Id continente` INT( 1 ) UNSIGNED NOT NULL ,
`Id país` INT( 3 ) UNSIGNED NOT NULL ,
`Id comunidad autónoma` INT( 2 ) UNSIGNED NOT NULL ,
`Id provincia` INT( 2 ) UNSIGNED NOT NULL ,
`Id localidad` INT( 5 ) UNSIGNED NOT NULL ,
`Id orientación sexual` INT( 1 ) UNSIGNED NOT NULL ,
INDEX ( `Id continente` ) ,
INDEX ( `Id país` ) ,
INDEX ( `Id comunidad autónoma` ) ,
INDEX ( `Id provincia` ) ,
INDEX ( `Id localidad` ) ,
INDEX ( `Id orientación sexual` ) ,
FOREIGN KEY ( `Id continente` ) REFERENCES `Continentes` ( `id` ) ON UPDATE CASCADE ON DELETE CASCADE ,
FOREIGN KEY ( `Id país` ) REFERENCES `Paises` ( `id` ) ON UPDATE CASCADE ON DELETE CASCADE ,
FOREIGN KEY ( `Id comunidad autónoma` ) REFERENCES `Comunidades autónomas` ( `id` ) ON UPDATE CASCADE ON DELETE CASCADE ,
FOREIGN KEY ( `Id provincia` ) REFERENCES `Provincias` ( `id` ) ON UPDATE CASCADE ON DELETE CASCADE ,
FOREIGN KEY ( `Id localidad` ) REFERENCES `Localidades` ( `id` ) ON UPDATE CASCADE ON DELETE CASCADE 
) ENGINE = innodb CHARACTER SET utf8 COLLATE utf8_general_ci;

[Carxl]

Hola yoguuu...

Mira una cosa es cierta para aclararte tu primer duda... Todo país tiene estados o departamentos, además estos departamentos tienen cuidades. Normalmente es hasta este nivel que se llega cuando estás realizando tu Db, 1. PAIS, 2. DEPARTAMENTO O ESTADO, 3. CUIDAD. Si quieres llegar mas allá, pues vendría siendo tu tabla "Localidades" aunque me parece que te complicarías mucho...

Eso de la tabla "Continente" me parece innecesaria por que hasta ahora solo existen 5 continentes no vas a poder administrar eso... o sí? Estarías haciendo una tabla por hacerla y ya... Mas bien en la tabla "Pais" dejaría un campo para el continente y borraría esa tabla.

Una cosa muy importante; en la tabla "Usuarios" borraría las siguientes FK:

FK Id continente
FK Id país

Porqué?? Si hablamos que llegaste hasta el nivel de CIUDAD, por lógica sabes que esa ciudad va a pertenecer a una departamento o estado y que a su vez dicho departamento o estado pertenece a un país y que ese país tiene un canpo continente... ves?? Con solo pedir la ciudad ya sabes por ende que a que departamento pertenece y por ende a que país. Lo mismo pasaría si vas a llegar hasta el nivel de Localidad... solo basta con saber la localidad, con eso ya sabrías a que ciudad pertenecería.

Y para tu otra pregunta, que si pueden haber campos nulos en FK..., claro que pueden haber!! Y para nada afectaría tu RI, aunque no sería lo mas ortodoxo, pero hay veces en las que es necesario.

Espero te haya servido y mas aún que me hayas entendido...

Saludos