Datamodeling, constraints and issue with my model

Question

I am designing an application that will allow for childminders/nannies to find babies to look after and parents to find childminders/nannies that will look after their children.

I would like to have a single ACCOUNT database table both for parents and childminders.

However, there are some slight differences between parent accounts and childminder accounts i.e.

• childminders can have a curriculum/resume attached to their account (materialized by a RESUME database table)
• childminder accounts are further refined by a childminder statuses (Nanny, Baby-sitter, etc.) (materialized by a CHILDMINDER_STATUS database table). A childminder can have one or more "childminder statuses".

I am not sure what the relationships between the ACCOUNT, RESUME and CHILDMINDER_STATUS should be...

P.S. I use MySQL server.

EDIT: I forgot to mention that an ACCOUNT belonging to a childminder has zero or more ADVERTISEMENT that belong only to a childminder. Same thing with ACCOUNTs belonging to a parent together with their ADVERTISEMENTs.

Answer 1

This works - does it answer your question? Other tables would follow in the same way.

create table account (
  id int unsigned auto_increment PRIMARY KEY,
  fname varchar(100),
  lname varchar(100),
  type enum('parent','childminder')
  -- etc - add other fields needed for both parents and childminders
) ENGINE=InnoDB;

create table resume (
  id int unsigned auto_increment PRIMARY KEY,
  account_id int unsigned,
  file_spec varchar(100),
  -- other fields as necessary
  CONSTRAINT `resume_account_id_fk` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`)
) ENGINE=InnoDB;

create table childminder_status (
  id int unsigned auto_increment PRIMARY KEY,
  account_id int unsigned,
  status_description varchar(100),
  -- other fields as necessary
  CONSTRAINT `childminder_status_account_id_fk` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`)
) ENGINE=InnoDB;