Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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' );
- 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 ;
- CREATE TABLE person (
- person_id int PRIMARY KEY
- -- Other fields...
- );
- CREATE TABLE civil (
- civil_id int PRIMARY KEY REFERENCES person (person_id)
- -- Other fields...
- );
- CREATE TABLE worker (
- worker_id int PRIMARY KEY REFERENCES person (person_id)
- -- Other fields...
- );
- CREATE TABLE event (
- event_id int PRIMARY KEY,
- person_id int REFERENCES person (person_id)
- -- Other fields...
- );
- CREATE TABLE person_type (
- person_type_id int PRIMARY KEY
- -- data: 1=civil, 2=worker
- -- Other fields (such as a label)...
- );
- CREATE TABLE person (
- person_id int PRIMARY KEY
- person_type_id int FOREIGN KEY REFERENCES person_type (person_type_id)
- -- Other fields...
- );
- CREATE TABLE civil (
- civil_id int PRIMARY KEY REFERENCES person (person_id)
- person_type_id int FOREIGN KEY REFERENCES person (person_type_id)
- -- Other fields...
- );
- CREATE TABLE worker (
- worker_id int PRIMARY KEY REFERENCES person (person_id)
- person_type_id int FOREIGN KEY REFERENCES person (person_type_id)
- -- Other fields...
- );
- CREATE TABLE event (
- event_id int PRIMARY KEY,
- person_id int REFERENCES person (person_id)
- -- Type is optional here, but you could enforce event for a particular type
- person_type_id int FOREIGN KEY REFERENCES person (person_type_id)
- -- Other fields...
- );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement