13

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 in an event table, now these guys rescue an animal (there is an animal table).

Finally, I want to have a table to store the event that a guy (worker, civil), saved an animal, but bow should I add a foreign key or how to know the id value of the civil or worker that did the job?

Now, in this design I do not know how to relate which person did the job if, I would had only a kind of person (aka civil) I would only store the civil_id vale in a person column in this last table... but how to know if it was civil or worker, do I need other "intermediate" table?

How to reflect the design of the following diagram in MySQL?

enter image description here

Additional details

I have modelled 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 an updated sqlfiddle.

migrated from stackoverflow.com Mar 13 '13 at 14:17

This question came from our site for professional and enthusiast programmers.

  • what is the purpose of table TYPE_PERSON when it only contains one column? – JW 웃 Mar 11 '13 at 1:36
  • 1
    Follow-up to this: stackoverflow.com/questions/15128222/… ? – PM 77-1 Mar 11 '13 at 1:40
  • @cMinor - You're asking "how to know the id of the civil or worker that did the job?" Do you actually know who did the job in real life (or imaginary, if this is a homework)? Do you have sufficient source data? – PM 77-1 Mar 11 '13 at 1:43
  • I'm getting used to inheritance, so I created a table person that would hold the types of people(worker,civil), then in event table, How to reference a person depending on how did the job( civil or worker)? – cMinor Mar 11 '13 at 1:50
  • 1
    I believe you would get better advice in Database Administrators – Pieter Geerkens Mar 11 '13 at 2:24
12

Since I made the diagram, I better answer ;)

Current relational databases unfortunately don't support the inheritance directly, therefore you need to transform it into "plain" tables. There are generally 3 strategies for doing so:

  1. All classes1 in a single table with NULL-able non-common fields.
  2. Concrete classes2 in separate tables. Abstract classes don't have the tables of their own.
  3. All classes in separate tables.

For more on what this actually means and some pros and cons, please see the links provided in my original post, but in a nutshell the (3) should probably be your default unless you have a specific reason for one of the other two. You can represent the (3) in the database simply like this:

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...
);

Unfortunately, this structure will let you have a person that is neither civil nor worker (i.e. you can instantiate the abstract class), and will also let you create a person that is both civil and worker. There are ways to enforce the former at the database level, and in a DBMS that supports deferred constraints3 even the latter can be enforced in-database, but this is one of the few cases where using the application-level integrity might actually be preferable.


1 person, civil and worker in this case.

2 civil and worker in this case (person is "abstract").

3 Which MySQL doesn't.

  • How can the latter be enforced in DBMS that support deferred constraints? (disallowing a person from being both civil and worker) – Gima Jun 20 '17 at 17:57
  • @Gima Please follow the link I have provided in the answer. – Branko Dimitrijevic Jun 21 '17 at 8:23
  • You claim current relational databases don't support inheritance. What about postgresql? postgresql.org/docs/9.6/static/ddl-inherit.html – Climax Dec 26 '17 at 14:45
  • @Climax I'm aware of PostgreSQL, but its implementation is only partial. From your link: "Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." – Branko Dimitrijevic Dec 26 '17 at 14:56
  • 1
    @naaz Foreign keys exist in both civil and worker. Perhaps you missed the short-hand syntax (just REFERENCES without FOREIGN KEY)? – Branko Dimitrijevic Apr 1 '18 at 6:36
5

There is no need for distinct Civil_ID and Worker_ID; just continue to use Person-ID as the key for all three tables: Person, Civil, and Worker. Add a column PersonType to Person with the two values "Civil" and "Worker".

This now represents the two sub-classes CivilClass and WorkerClass of the abstract base class PersonClass as sub-entities Civil and Worker of the base entity Person. You get a nice correspondence between the data model in the DB with the object model in the application.

  • I have done a sqlfiddle sqlfiddle.com/#!2/1f6a4/1 but I do not know how to join other table, could you please point your answer here in the sqlfiddle? – cMinor Mar 11 '13 at 3:35
  • There are no "distinct" civil_id and worker_id - they are the same thing as person_id, just named differently - look at the FK1 (foreign key) marker in front of them. – Branko Dimitrijevic Mar 11 '13 at 20:12
4

Your case is an instance of class/subclass modeling. Or, as you have diagrammed it in ER, generalization/specialization.

There are three techniques that will help you design mysql tables to cover this case. They are called Single Table Inheritance, Class Table Inheritance, and Shared Primary key. You can read up on them in the info tab from the corresponding tag over in SO.

https://stackoverflow.com/tags/single-table-inheritance/info

https://stackoverflow.com/tags/class-table-inheritance/info

https://stackoverflow.com/tags/shared-primary-key/info

Single table inheritance is useful for simple cases where the presence of NULLs doesn't cause problems. Class table inheritance is better for more complicated cases. Shared primary key is a good way to enforce one-to-one relationships, and to speed up joins.

1

You can create a person type table and add a field to all tables needing the type enforcement. Then create foreign keys. Here is an example deriving from yours...

    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...
    );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.