Tablas cruzadas

[Kritik]

Estoy diseñando una base de datos, que me cubra unas necesidades un tanto especiales. Así pues, pongo las 2 tablas que me crean la duda que tengo.

Tengo la tabla compras:

Código SQL:

CREATE TABLE MAYOR_COMPRAS(
PARTIDA_COMPRA NUMBER(10) NOT NULL,
FECHA_COMPRA DATE DEFAULT SYSDATE NOT NULL,
COD_PROD01 NUMBER(5),CANT_PROD01 NUMBER(4),PRECIO_UD_02 NUMBER(6,2),
COD_PROD02 NUMBER(5),CANT_PROD02 NUMBER(4),PRECIO_UD_02 NUMBER(6,2),
COD_PROD03 NUMBER(5),CANT_PROD03 NUMBER(4),PRECIO_UD_03 NUMBER(6,2),
...
...
COD_PROD50 NUMBER(5),CANT_PROD50 NUMBER(4),PRECIO_UD_50 NUMBER(6,2),
COD_EMPLEADO VARCHAR2(15) NOT NULL,
CONSTRAINT FK_COMPRAS FOREIGN KEY (COD_EMPLEADO) REFERENCES EMPLEADOS,
CONSTRAINT PK_COMPRAS PRIMARY KEY (PARTIDA_COMPRA)
);
 

Y tengo la tabla Productos.
Lo que complica la historia es que tengo que distinguir el producto del mismo producto según el momento de comprarlo, Pero no solo del día (porque en el mismo día varios comerciales de compras pueden comprarme el mismo producto) así pues, lo hago por medio de la partida de compra:

Código SQL:

CREATE TABLE MAYOR_PRODUCTOS(
PARTIDA_COMPRA NUMBER(10),
COD_PROD NUMBER(5),
MARCA VARCHAR2(15) NOT NULL,
MODELO VARCHAR2(30) NOT NULL,
PRECIO_COMPRA NUMBER(6,2),
STOCK NUMBER(5),
STOCK_RESERVADO NUMBER(5),
STOCK_TOTAL NUMBER(5) AS (STOCK+STOCK_RESERVADO),
CONSTRAINT FK_PRODUCTOS FOREIGN KEY (PARTIDA_COMPRA) REFERENCES COMPRAS,
CONSTRAINT PK_PRODUCTOS PRIMARY KEY (PARTIDA_COMPRA,COD_PROD)
);
 

Sin embargo, esto hace que las partidas de compras hacen que se me crucen las tablas, por lo tanto... al estar cerradas no me dejan introducir más registro desde el momento en el que quedan cruzadas bidireccionalmente.

A ver qué se os ocurre para solucionar el problema. ¿Quizá hacer un ID_COMPRA en la tabla compras y hacer que sea ese el PK de compras? Pero entonces es como si tuviese 2 campos únicos e identificativos que sirven para identificar de manera inequívoca una compra de otra (No es esa la función del PK? No sería como tener información repetida?)

[Kritik]

Por otra parte... si alguien pudiese darme un ejemplo parecido a este diseño de talbas:

http://www.campusmvp.es/recursos/ima...rthwind_EF.png

Pero en español, estaría muy agradecido.

[Libras]

tus tablas no estan normalizadas, y para que guardar tantas veces

COD_PROD02 NUMBER(5),CANT_PROD02 NUMBER(4),PRECIO_UD_02 NUMBER(6,2)

no seria mejor tener todos esos campos en una tabla separada???

y la tabla productos:

Código SQL:

CREATE TABLE MAYOR_PRODUCTOS(
PARTIDA_COMPRA NUMBER(10),
COD_PROD NUMBER(5),
MARCA VARCHAR2(15) NOT NULL,
MODELO VARCHAR2(30) NOT NULL,
PRECIO_COMPRA NUMBER(6,2),
STOCK NUMBER(5),
STOCK_RESERVADO NUMBER(5),
STOCK_TOTAL NUMBER(5) AS (STOCK+STOCK_RESERVADO),
CONSTRAINT FK_PRODUCTOS FOREIGN KEY (PARTIDA_COMPRA) REFERENCES COMPRAS,
CONSTRAINT PK_PRODUCTOS PRIMARY KEY (PARTIDA_COMPRA,COD_PROD)
);
 

[/B]
de aqui podemos sacar 2 tablas la tabla de marcas y la tabla de modelos
ademas creo que el stock_total lo puedes calcular no es necesario que este en la tabla........

[Kritik]

He decidido unificar compras y productos en una sola tabla.
La cual es la siguiente:

Código SQL:

CREATE TABLE PRODUCTOS(
PARTIDA_COMPRA NUMBER(10) NOT NULL,
FECHA_COMPRA DATE DEFAULT SYSDATE NOT NULL,
COD_EMP VARCHAR2(15) NOT NULL,
COD_PROD NUMBER(5) NOT NULL,
CANTIDAD NUMBER(4) NOT NULL,
PRECIO_UD NUMBER(6,2) NOT NULL,
MARCA VARCHAR2(15),
MODELO VARCHAR2(30)
CONSTRAINT PK_PRODUCTOS PRIMARY KEY (PARTIDA_COMPRA,COD_PROD)
);
 

[/CODE]
Pues quiero diferenciar el producto de él mismo según la partida de compra.

Sin embargo, ahora en mi tabla almacén quiero juntar todos los productos del mismo código, dándome igual de qué partida sean. Y a su vez, en la tabla almacén quiero poder restar stock al stock libre para pasarlo a stock reservado, teniendo un stock total como la suma de ambas. (Para hacer pedidos tengo que hacer una reserva del producto en el almacén, pero pudiéndolo controlar para poderla echar hacia atrás.

Como puedo hacer eso?

PD: Lo que define el código del producto es la marca y el modelo, y son tratados como informaciones suplementarias, no informaciones principales. La información principal en este caso es el código del producto. Digamos que yo compro y vendo códigos de productos, lo que sean los productos me dan igual. Es algo solo descriptivo y suplementario. Por lo tanto no veo el motivo de hacer una tabla de marcas y otra de modelos.

Ejemplo:
Cod_Prod 21421 Marca: Ricola Modelo: Caramelo Limón.
Cod_Prod 21422 Marca: Ricola Modelo: Caramelo Fresa.
Cod_Prod 26345 Marca: Philips Modelo: Bombilla 60W Alójena