I have some tables where I store data and depending on the type of person (worker,civil) that did a job I want to store it a table 'event', now these guys rescue an animal (thereis a table animal).
Finally I want to have a table to store the event that a guy (WORKER,CIVIL), saved an animal, but How should I add a foreign key or how to know the id of the civil or worker that did the job?
Now in this design I do not know how to relate whose person did the job if, I would had only a kind of person (aka civil) i would only store the civil_id in person field in this last table....but how to know if it was civil or worker, do I need other intermediate table?
How to make this design in mysql?
could you help me with the sqlfiddle?
UPDATE
I have model it the following way:
DROP TABLE IF EXISTS `tbl_animal`;
CREATE TABLE `tbl_animal` (
id_animal INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL DEFAULT "no name",
specie VARCHAR(10) NOT NULL DEFAULT "Other",
sex CHAR(1) NOT NULL DEFAULT "M",
size VARCHAR(10) NOT NULL DEFAULT "Mini",
edad VARCHAR(10) NOT NULL DEFAULT "Lact",
pelo VARCHAR(5 ) NOT NULL DEFAULT "short",
color VARCHAR(25) NOT NULL DEFAULT "not defined",
ra VARCHAR(25) NOT NULL DEFAULT "not defined",
CONSTRAINT `uc_Info_Animal` UNIQUE (`id_animal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tbl_animal` VALUES (1,'no name', 'dog', 'M','Mini','Lact','Long','black','Bobtail');
INSERT INTO `tbl_animal` VALUES (2,'peluchin', 'cat', 'M','Mini','Lact','Long','white','not defined');
INSERT INTO `tbl_animal` VALUES (3,'asechin', 'cat', 'M','Mini','Lact','Corto','orange','not defined');
DROP TABLE IF EXISTS `tbl_person`;
CREATE TABLE `tbl_person` (
type_person VARCHAR(50) NOT NULL primary key
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tbl_person` (type_person) VALUES ('Worker');
INSERT INTO `tbl_person` (type_person) VALUES ('Civil');
DROP TABLE IF EXISTS `tbl_worker`;
CREATE TABLE `tbl_worker`(
id_worker INTEGER NOT NULL PRIMARY KEY,
type_person VARCHAR(50) NOT NULL ,
name_worker VARCHAR(50) NOT NULL ,
address_worker VARCHAR(40) NOT NULL DEFAULT "not defined",
delegation VARCHAR(40) NOT NULL DEFAULT "not defined",
FOREIGN KEY (type_person) REFERENCES `tbl_person` (type_person),
CONSTRAINT `uc_Info_worker` UNIQUE (`id_worker`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tbl_worker` VALUES (1,'Worker','N_CEDENTE1', 'DIR Worker 1', 'DEL');
INSERT INTO `tbl_worker` VALUES (2,'Worker','N_worker1', 'DIR Worker 2', 'DEL');
INSERT INTO `tbl_worker` VALUES (3,'Worker','N_worker2', 'address worker','delegation worker');
DROP TABLE IF EXISTS `tbl_civil`;
CREATE TABLE `tbl_civil`(
id_civil INTEGER NOT NULL PRIMARY KEY,
type_person VARCHAR(50) NOT NULL ,
name_civil VARCHAR(50) ,
procedence_civil VARCHAR(40) NOT NULL DEFAULT "Socorrism",
FOREIGN KEY (type_person) REFERENCES `tbl_person` (type_person),
CONSTRAINT `uc_Info_civil` UNIQUE (`id_civil`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tbl_civil` VALUES (1,'Civil','N_civil1' , 'Socorrism');
CREATE TABLE `tbl_event` (
id_event INTEGER NOT NULL,
id_animal INTEGER NOT NULL,
type_person VARCHAR(50) NOT NULL ,
date_reception DATE DEFAULT '2000-01-01 01:01:01',
FOREIGN KEY (id_animal) REFERENCES `tbl_animal` (id_animal),
FOREIGN KEY (type_person ) REFERENCES `tbl_person` (type_person ),
CONSTRAINT `uc_Info_ficha_primer_ingreso` UNIQUE (`id_animal`,`id_event`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tbl_event` VALUES (1,1, 'Worker','2013-01-01 01:01:01' );
INSERT INTO `tbl_event` VALUES (2,2, 'Civil','2013-01-01 01:01:01' );
However, is there a way to get rid of nulls? the queries I have are:
SELECT a.*,b.*,z.*
FROM tbl_event a
left JOIN tbl_worker b
ON a.type_person = b.type_person
left JOIN tbl_animal z
ON z.id_animal = a.id_animal ;
SELECT a.*,b.*,z.*
FROM tbl_event a
left JOIN tbl_civil b
ON a.type_person = b.type_person
left JOIN tbl_animal z
ON z.id_animal = a.id_animal ;
Here is updated sqlfiddle
TYPE_PERSON
when it only contains one column? – JW 웃 Mar 11 at 1:36