[Php-avanzado] Parte de la estructura de la base de datos + Consulta

[Leonardo Tadei - Pegasus Tech Supply]

Hola Javier,

El lun, 07-09-2009 a las 16:19 -0300, NeiKeR escribió:
> Buenas..
> Agregué la gestion de musicos y generos..
> 
> http://img195.imageshack.us/img195/4441/exportc.png
> 


> Create table bands (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	name Char(100) NOT NULL,
> 	ltheme Char(100),
> 	logo Char(20),
> 	syear Year(4),
> 	eyear Year(4),
> 	info Text,
> 	status Int UNSIGNED NOT NULL,
> 	label Char(100),
> 	city Mediumint UNSIGNED NOT NULL,
> 	display Mediumint UNSIGNED BINARY,
> 	UNIQUE (id),
> 	UNIQUE (logo),
>  Primary Key (id)) ENGINE = MyISAM;

	Que la clave primaria además sea única, es una especio de redundancia
de las bases de datos... por definición, la clave primaria siempre es
única.

> Create table discs (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	name Char(100),
> 	date Int UNSIGNED,
> 	type Mediumint UNSIGNED,
> 	tlist Text,
> 	front Char(20),
> 	info Text,
> 	UNIQUE (id),
> 	UNIQUE (front),
>  Primary Key (id)) ENGINE = MyISAM;

	Idem!

> Create table events (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	place Int UNSIGNED NOT NULL,
> 	day Int UNSIGNED,
> 	hr Int UNSIGNED,
> 	info Text,
> 	img Char(20),
> 	UNIQUE (id),
>  Primary Key (id)) ENGINE = MyISAM;

	Para la fecha y hora, usá un campo DateTime.

> Create table places (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	name Char(100) NOT NULL,
> 	city Mediumint UNSIGNED NOT NULL,
> 	info Text,
> 	gmaps Char(150),
> 	UNIQUE (id),
>  Primary Key (id)) ENGINE = MyISAM;
> 
> Create table bford (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	disc_id Int UNSIGNED NOT NULL,
> 	band_id Int UNSIGNED NOT NULL,
> 	UNIQUE (id),
>  Primary Key (id)) ENGINE = MyISAM;
> 
> Create table cities (
> 	id Mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
> 	dep_id Mediumint UNSIGNED NOT NULL,
> 	name Char(100),
> 	UNIQUE (id),
>  Primary Key (id)) ENGINE = MyISAM;
> 
> Create table parts (
> 	id Mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
> 	name Char(20),
> 	prov_id Mediumint UNSIGNED NOT NULL,
> 	UNIQUE (id),
> 	UNIQUE (name),
>  Primary Key (id)) ENGINE = MyISAM;

	Lo venía leyendo en inglés y este nombre me desentonó... debería ser
counties...
	Deja el nombre como quieras, solo te contaba la anécdota.

> Create table provs (
> 	id Mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
> 	name Char(20),
> 	UNIQUE (id),
> 	UNIQUE (name),
>  Primary Key (id)) ENGINE = MyISAM;
> 
> Create table bfore (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	event Int UNSIGNED NOT NULL,
> 	band Int UNSIGNED NOT NULL,
> 	UNIQUE (id),
>  Primary Key (id)) ENGINE = MyISAM;
> 
> Create table status (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	name Char(50) NOT NULL,
> 	UNIQUE (id),
>  Primary Key (id)) ENGINE = MyISAM;
> 
> Create table genres (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	name Char(50) NOT NULL,
> 	UNIQUE (id),
> 	UNIQUE (name),
>  Primary Key (id)) ENGINE = MyISAM;
> 
> Create table gforb (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	id_band Int UNSIGNED NOT NULL,
> 	id_genre Int UNSIGNED NOT NULL,
> 	UNIQUE (id),
>  Primary Key (id)) ENGINE = MyISAM;
> 
> Create table musi (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	name Char(70),
> 	info Text,
> 	UNIQUE (id),
>  Primary Key (id)) ENGINE = MyISAM;
> 
> Create table instr (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	name Char(50),
> 	UNIQUE (id),
> 	UNIQUE (name),
>  Primary Key (id)) ENGINE = MyISAM;
> 
> Create table mforb (
> 	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
> 	id_band Int UNSIGNED NOT NULL,
> 	id_mus Int UNSIGNED NOT NULL,
> 	id_ins Int UNSIGNED NOT NULL,
> 	UNIQUE (id),
>  Primary Key (id)) ENGINE = MyISAM;

	Te pregunto para que me des contexto: y si un músico toca en una banda
varios instrumentos, tendrá un registro en esta tabla para cada
instrumento que toca?

	Por otra parte, tiene sentido que tenga UNIQUE (id_band,id_mus,id_ins).

	La normalización está correcta.
	Las cosas que te pregunto son más bien ratificaciones, pero no veo nada
que desentone respecto a la NF3.

	Saludos!
-- 

Leonardo Tadei
leonardot en pegasusnet.com.ar
http://blog.pegasusnet.com.ar
Firma pública: http://www.pegasusnet.com.ar/LeonardoTadei-public.key