0

I'm designing table relationship.

I have an one question.

An Example)

Some customer want to contract car or bike.

Customer must contract an one only.

I'm thinking for this case table relationship.

1.

CREATE TABLE IF NOT EXISTS `contract` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '[ID]',
  `no` VARCHAR(20) NOT NULL COMMENT '[NO]',
  `type` TINYINT UNSIGNED NOT NULL COMMENT '[contract target]..1:car,2:bike',
  `type_id` INT UNSIGNED NOT NULL COMMENT '[target table id]',
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

2.

CREATE TABLE IF NOT EXISTS `contract` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '[ID]',
  `no` VARCHAR(20) NOT NULL COMMENT '[NO]',
  `car_id` INT UNSIGNED NOT NULL COMMENT '[car table id]',
  `bike_id` INT UNSIGNED NOT NULL COMMENT '[bike table id]',
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

car, bike table primary key is id.


case 1.

If type value is 1. Refer car table. 2 is bike table.

This case can't set foreign key constraint. but size is smaller than case2.

case 2.

If contract target is car. bike_id column value is 0.


If you have a idea Please tell me.

Thank you.

  • 1
    Record size is (4+(20+1)+1+4)+6+7=43 bytes for case 1 and 46 bytes for case 2. Do you really think that 7% size save (without taking into account the block granularity) is better than FK constraint existence? – Akina Jul 12 '18 at 5:06
  • @Akina Thank you for your reply. I choose case 2! – jonggu Jul 12 '18 at 5:12

Your Answer

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

Browse other questions tagged or ask your own question.