Re: Simple database design question

On Sep 30, 9:03 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> Mark S. (UK) wrote:
> > Bob Badour 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.
>
> > In method 1, if phone_sim_id is NULL, then is it not related to 0
> > records?  Or am I missing something?
>
> Yes, you are missing a healthy aversion to NULL.

I had the feeling you were going to mention that :-) I have been wrestling with myself on that. For years I had an aversion to NULL. ALL of my fields used to say NOT NULL next to them in my definitions, however it seemed a useful way of saying "this object is not yet associated" ...

As far as I'm aware, there are two alternatives; 1) having a dummy object that unassociated objects are related to... OR having a separate relation table to associate the objects, and without an entry in that relation table, there is no relation. That way, I could get rid of using NULL.

Most of my fields are at least still NOT NULL.

>
>
>
> >>>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.
>
> > So the predicate would be "phone xxxxx uses sim xxxxx"..
>
> > Could you elaborate a little more or maybe direct me at some text that
> > might help?  Or another hint?  I would like to figure it out for myself
> > if I can, but I think I'm mentally stuck in one way of thinking here; I
> > can only think of relating the two items of data either as I've
> > described above or using a dedicated relation table.. or possibly an index.
>
> > How about:
>
> > PHONE:
> >   phone_id
>
> > SIM:
> >   sim_id
>
> > RELATION:
> >   phone_id
> >   sim_id
>
> > unique index on phone_id, sim_id ...
>
> > Am I at least on the right lines?
>
> Only one (compound) candidate key?

Sorry, I have missed out some important information:

PHONE:
  phone_id (PK)

SIM:
  sim_id (PK)

RELATION:
  phone_id
  sim_id

unique index on phone_id, sim_id

So now I've got a couple of primary keys as well as the compound index..

Mark.

--