0

A database schema design question:

Suppose I want to design a database with Robots, Technicians and Sites.

I want to use my database constraints to limit the data to prevent entering some nonsensical cases.

For example, I manufacture a Robot; when I lease him out, I assign and ship him to a Site.

I enter a record in my database - a Robot can only be at one Site, so I could give the Robots table a nullable SiteID Foriegn Key field to Sites to represent that limit. No robot can then be assigned to two Sites in the database.

create table Sites (
SiteID int not null,
Name varchar(1024) not null,
primary key (SiteID)
);

create table Robots (
RobotID int not null,
SiteID int nullable,
primary key (RobotID),
constraint 'fk_robots_sites_siteid' foreign key (SiteID) references Sites(SiteID)
);

Good so far.

So now I want to add Technicians to my database:

create table Technicians (
TechnicianID int not null,
name varchar(1024) not null,
primary key (TechnicianID)
);

What if... I wanted to have some of my Robots act as a chauffeur to my Technicians? Then a Robot could be assigned to either a Site or to a Technician - but not both.

I can't figure out how to implement this constraint in the schema.

Suggestions?

1 Answer 1

1

either a Site or to a Technician - but not both.

alter table Robots add Check (SiteID is null or TechnicianID is null)
1
  • Thanks. I'll tinker with it but it looks portable to MySQL and MS SQL - perfect.
    – rich p
    Commented May 14 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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