0

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?

2 Answers 2

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.

0

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

Your Answer

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

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