Re: Simple database design question

From: Mark S. (UK) <"Mark>
Date: Tue, 30 Sep 2008 20:24:26 +0100
Message-ID: <pJ6dnZ_af-N24X_VRVnytAA_at_pipex.net>

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?

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

Thanks,

Mark.

--

Received on Tue Sep 30 2008 - 21:24:26 CEST