Re: Simple database design question

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. Received on Tue Sep 30 2008 - 18:23:33 CEST