Re: Simple database design question

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 01 Oct 2008 17:20:23 -0300
Message-ID: <48e3db8c$0$5454$9a566e8b_at_news.aliant.net>

JOG wrote:

> On Sep 30, 4:07 pm, "Mark S. (UK)" <ma..._at_nospamhere.com> 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.

> 
> 
> Here you have described two unary predicates: "exists_sim(sim)" and
> "exists_phone(phone)".
> Congratulations you have identified you're first two relations.
> 
> 

>>Rule 1: A sim card can either be in a phone, or not in a phone.

> 
> 
> Here you have identified the binary predicate: "contains(phone, sim)"
> This is you're second relation. I think we'd agree the items in this
> relation must exist... great, we have relations that state that
> already, so two foreign key constraints linking to them are required.
> 
> 

>>It cannot be in more than one phone.

> 
> 
> Here you have identified a first uniqueness constraint on the
> "contains" relation.
> 
> 

>>Rule 2: A phone can either have one sim inserted, or no sim inserted.

> 
> 
> And there's your second uniqueness constraint on the "contains"
> relation. Implement them and job done - and with not a null in sight.
> Breaking your problems down into predicates in this fashion is always
> the way forward
> 
> Now tell me what would happen if a phone was deleted from the
> "exists_phone" table?

Perhaps more urgently, what would happen if a sim was deleted from the SIMS table?

>>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.
>>
>>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.
>>
>>Thanks,
>>
>>Mark.

>
> Received on Wed Oct 01 2008 - 22:20:23 CEST