Ayuda con diseño

[a_moreno]

Hola y buen día a todos, verán me encuentro diseñando un modelo de base de datos pero resulta que ya llevo atorado algo de tiempo, el problema es este :

Tengo que diseñar una base de datos para el control del expediente clinico de un hospital, se tienen que registrar el empleado, el dependiente del empleado (madre, esposa,hijos, etc) y el expediente de cada uno.

En cuanto a las tablas de empleado y dependiente, pues las relaciono 1:N, es decir, un empleado puede tener varias personas que dependan de el para el seguro médico (varios hijos o hasta esposas) y hasta ahí no hay problema.

Lo que no se es como relacionar la tabla expediente con empleado y dependiente, al principio pareceria que estas van realcionadas con expediente asi nada mas, pero el problema es que entonces en expediente tendria dos llaves foraneas, una de empleado y la otra de dependiente, entonces cuando realice una alta en la tabla de expediente, no podre saber a quien se refiere y ni modo de aislar la tabla expediente sin unirla.

Aqui les dejo los scripts que realice, para mayor claridad del asunto

Código:

CREATE TABLE empleado
(
   ficha           CHAR(10),
   a_paterno       CHAR(15),
   a_materno       CHAR(15),
   nombre          CHAR(25),
   genero          CHAR(2),
   fecha_nac       DATE,
   fecha_alta      DATE,
   direccion       CHAR(50),
   telefono        CHAR(15),
   tipo_trabajador CHAR (10),
   CONSTRAINT      pk_empleado PRIMARY KEY (ficha)
);

CREATE TABLE dependiente
(
   ficha            CHAR(10),
   ficha_d          CHAR(10),
   a_paterno        CHAR(15),
   a_materno        CHAR(15),
   nombre           CHAR(25),
   genero           CHAR(2),
   fecha_nac        DATE,
   fecha_alta       DATE,
   direccion        CHAR(50),
   telefono         CHAR(15),
   tipo_dependiente CHAR(2),
   CONSTRAINT pk_dependiente PRIMARY KEY (ficha_d),
   CONSTRAINT fk_dependiente FOREIGN KEY (ficha) REFERENCES empleado (ficha) ON DELETE CASCADE
);

CREATE TABLE expediente
(
   ficha                 CHAR(10),
   historial             CLOB,
   status                CHAR(10),
   fecha_ultima_consulta DATE,
   CONSTRAINT fk_expediente FOREIGN KEY (ficha)  REFERENCES empleado (ficha)       ON DELETE CASCADE,
   CONSTRAINT fk_expediente2 FOREIGN KEY (ficha) REFERENCIES dependiente (ficha_d) ON DELETE CASCADE,
);

Gracias por todo XD

[huesos52]

Hablando por hablar, me imagino que tanto empleados como dependientes tienen un único expediente. Si es así, lo mejor sería tener una tabla expediente con un id_expediente y que las tablas empleado y dependiente hagan referencia a este id.

Si no es así, te recomendaría codificar diferente tanto empleados como dependiente y dejar el modelo como lo planteas. Por ejemplo... los códigos para empleados sean e1,e1,e3..eN. y para dependientes d1,d2..dN.

De tal forma, haya alguna forma de comparar mediante una consulta SQL que tipo de expedientes se generan.


Un saludo

[alguienmas]

yo pondria empleados y dependientes en la misma tabla...... digamos "persona" y la tabla "persona" tendria una relacion 1:n con sigo misma, asi sabrias que es un empleado por que la columna que tendria la key de quien depende, estaria nula.
Ahora la relacion de "persona" a expediente es de 1:n, independiente de si es empleado o dependiente.

Espero haber sido claro, saludos

[a_moreno]

Gracias por responder XD

alguienmas, ¿pero que pasaria con la llave foránea de expediente? esa llave foránea haría referencia a la llave primaria de la tabla persona, o sea el empleado, quiere decir que el expediente solo podria guardar llaves que tengan que ver con el empleado.... corríjeme si estoy mal .

Gracias