Re: Simple database design question

On Sep 30, 5:23 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> Mark S. (UK) wrote:
> > Hi all,
> > I have 2 tables, "sims" (as in mobile phone sim cards) and "phones" (as
> > in mobile phones).  I can add any number of sim cards to the "sims"
> > table and any number of mobile phones to the "phones" table.  Simple enough.
>
> > Rule 1: A sim card can either be in a phone, or not in a phone.  It
> > cannot be in more than one phone.
>
> > Rule 2: A phone can either have one sim inserted, or no sim inserted.
>
> > So the relationship between the phone and sim is 0:1 to 0:1, I think..
>
> > There's several ways I could implement this, but the two most obvious to
> > me so far are:
>
> > ### Method 1
>
> > SIMS table:
> >  sim_id
>
> > PHONES table:
> >  phone_id
> >  phone_sim_id references sims(sim_id)
>
> > ### Method 2
>
> > PHONES table:
> >  phone_id
>
> > SIMS table:
> >  sim_id
> >  sim_phone_id references phones(phone_id)
>
> > So problem number 1 is, which way round makes more sense?  My first
> > instinct was method 1.  It seemed to make sense to say "the phone has
> > this sim card", though I suppose you could just as easily turn that on
> > its head.
>
> Neither of the above handle both 0 cases.
>
> > Problem number 2 is, using either method above can break the rules and
> > create an impossible situation.  For example, several phones could
> > reference the same sim card in method 1, and several sim cards could
> > reference the same phone in method 2.  Would the proper way to ensure
> > integrity in this case be to add a "unique" modifier to the reference
> > field?  e.g:
>
> > phone_sim_id references sims(sim_id) unique
>
> > Any advice on the above would be much appreciated.
>
> If one thinks about the relations above as predicates instead of as
> tables, one naturally creates a much better design than either of the above.

I have thought of another solutions where the primary key of the sim table is also a foreign key that relates to the phone table : -

PHONE
  phone_id (PK)

SIM
  sim_id (PK, FK PHONE(phone_id))

Unfortunately though that stops the sim card being able to exist without being related to a phone, since the primary key must not be null.

The whole idea is that sim cards and phones can be bought and added to the database. At some point, the sim can be associated to ONE phone (and that ONE phone can only be associated with that ONE sim). The phone cannot have many sims, and the sims cannot have many phones.

Method 1 is working at the moment:

PHONE
  phone_id (PK)
  phone_sim_id (FK SIM(sim_id) UNIQUE)

SIM
  sim_id (PK)

This is ensuring the integrity of all the rules I've mentioned, however from what you've said I feel I may be missing something, and may well kick myself once I figure out what it is....

Mark.

--