Re: Simple database design question
Date: Wed, 01 Oct 2008 17:20:23 -0300
> On Sep 30, 4:07 pm, "Mark S. (UK)" <ma..._at_nospamhere.com> wrote: >
>>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
>> phone_sim_id references sims(sim_id)
>>### Method 2
>> 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
>>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
>>phone_sim_id references sims(sim_id) unique
>>Any advice on the above would be much appreciated.
> Received on Wed Oct 01 2008 - 22:20:23 CEST