Phones and SIMS table

Question

I have to design a table of Mobile Phones and SIMS. Some phones have more than 1 SIM slot (2). Some phones have no SIMS (or only 1), and any 1 SIM can only be in 0 or 1 phone(s).

A table that has PHONE_ID, SIM_ID is the starting point. Both columns are nullable. A primary key of (PHONE_ID, SIM_ID) allows for any phone to have more than 1, but it also unfortunately allows for any 1 SIM to be in more than 1 phone! How do I ensure that any 1 SIM can only be in 1 phone, while still allowing any number of phones to have no SIMS?

Answer 1

A table

You have two types of entity so almost certainly need two tables to model them. As the relationship is one-or-zero-to-many from the SIM side it should have a nullable foreign key to the phone table. Something like

Sim                  
============         Phone
ID      (PK)         =======
inPhone (FK)  ---->  ID (PK)

That way a SIM is either in a phone or floating. If you want to enforce the maximum number of SIMs in each phone at the schema level you'll need custom check constraints or triggers.

Answer 2

I'll simply make a FK on the "sim" table to phone. A phone can have multiple sims or 0.