Re: Simple database design question

From: Mark S. (UK) <"Mark>
Date: Tue, 30 Sep 2008 21:21:22 +0100
Message-ID: <OO-dndiAuY7fF3_VnZ2dnUVZ8uydnZ2d_at_pipex.net>


Bob Badour 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.
> 
> 

>>>> 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?

Those two posts from "marks_at_alienmuppet.com" are from me Bob. I had to switch to using google groups to send posts as my ISP's news service went down temporarily.

Mark. Received on Tue Sep 30 2008 - 22:21:22 CEST

Original text of this message