Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For example, in my app I have some class called Contact:

case class Contact(name: String, email: String, phone: String)

And several other classes have one-to-many relation to that contact (for example, there are Companies and Houses).

Currently, I have database table for Contacts set up as something like this:

CREATE TABLE `Contacts` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `entityId` bigint(20) NOT NULL,
 `entityType` tinyint(4) NOT NULL,
 `name` varchar(300),
 `phone` varchar(300),
 `email` varchar(300),
  PRIMARY KEY (`id`)
);

where entityType is storing the "code" that determines the entity type this contact belongs to (1 for Company, 2 for House, etc), and entityId points to the row in Companies or Houses table. Companies and Houses tables are defined as follows:

CREATE TABLE `Companies` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `Houses` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `address` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

So, when I need to get all contacts for some Company (which has id = 1234 in this example), I execute the following query:

select * from Contacts c where c.entityType = 1 and c.entityId = 1234;

Now, I understand that this is awful - at the very least, I can't create a foreign key on entityId, thus I can't ensure that my data is consistent.

The alternative will be to create a separate Contacts table for each entity type (CompanyContacts, HouseContacts, etc) but that will result in lots of duplication in both database and application code.

Is there a better way to do this?

share|improve this question
2  
To the downvoter - could you please tell me the reason for the downvote? I'm new to the site, so I probably don't know all the rules, but I need feedback to improve. –  Rogach Sep 22 '13 at 4:49
    
Could you please show some sample data to test ? –  HAL9000 Sep 22 '13 at 4:56
    
@CORRUPT - Sure. I added the two other tables example definitions, and sample of the query to retrieve the contacts. –  Rogach Sep 22 '13 at 5:06
    
Use The Party Model. –  Neil McGuigan Sep 22 '13 at 9:30

1 Answer 1

up vote 1 down vote accepted

I might suggest to reorganize schema, to make it more scalable.

  1. DROP columns entityId and entityType.
  2. CREATE an Objects (or Entities) table which will store public / shared information for all objects / entities. It's primary key will migrate to Companies and to Houses with 1:1 relation. For one object might exist only one company OR only one house. Also, you will have to remove AUTO_INCREMENT from Companies and Houses, because in this case they will no longer generate identifiers themselves.
  3. Add foreign key to Contacts table from shared Objects table - contacts might exist for any type of object.

Structure might look like this:

Schema

You might obtain entity type by following query:

SELECT
    cnt.*,
    CASE
        WHEN NOT cmp.id IS NULL THEN 1
        WHEN NOT hs.id IS NULL THEN 2
    END as entityType
FROM
    Contacts as cnt
        INNER JOIN Objects as obj ON cnt.objectId = obj.id
        LEFT JOIN Companies as cmp ON cmp.id = obj.id
        LEFT JOIN Houses as hs ON cmp.id = hs.id

Also, note that entityId becomes objectId in this case.


Here is a CREATE-script for benchmarking and test:

CREATE TABLE IF NOT EXISTS `Objects` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `shared_property1` VARCHAR(45) NULL,
  `shared_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Companies` (
  `id` BIGINT NOT NULL,
  `company_name` VARCHAR(300) NULL,
  `private_property1` VARCHAR(45) NULL,
  `private_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `FK_Companies_Objects`
    FOREIGN KEY (`id`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Houses` (
  `id` BIGINT NOT NULL,
  `address` VARCHAR(300) NULL,
  `private_property1` VARCHAR(45) NULL,
  `private_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `FK_Houses_Objects`
    FOREIGN KEY (`id`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Contacts` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `objectId` BIGINT NOT NULL,
  `name` VARCHAR(300) NULL,
  `phone` VARCHAR(300) NULL,
  `email` VARCHAR(300) NULL,

  PRIMARY KEY (`id`),

  INDEX `FK_Contacts_Objects` (`objectId` ASC),

  CONSTRAINT `FK_Contacts_Objects`
    FOREIGN KEY (`objectId`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;
share|improve this answer
    
That is the standard way to model subclassing in ORM's. It is quite a nice approach, with only one drawback (in this situation) - the application code is going to become quite complicated. –  Rogach Sep 22 '13 at 6:16
    
@Rogach Yeah. Still, application code is more easier to change, than a large database records. Another way is to add a lot of foreign keys in a Contacts table for each entityType, i.e. companyId NULL, houseId NULL. But I think it might become an overkill after adding 50-60 entities. Consider. –  HAL9000 Sep 22 '13 at 6:20
    
As a second thought, given the fact that I almost don't have shared fields between objects in this case (only id), the application code actually can stay almost the same. I think that's the best approach. –  Rogach Sep 22 '13 at 6:20
    
@Rogach you might create a view to simplify selection. But INSERTS might get complicated for a case, that I've described. –  HAL9000 Sep 22 '13 at 6:22
1  
No, duplicating id columns for each entityType is absolutely horrible - just think about the poor guy who will need to maintain the db after me. He'll probably submit it to DailyWTF ;) –  Rogach Sep 22 '13 at 6:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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