Re: Simple database design question

[Quoted] 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

[Quoted] Now tell me what would happen if a phone was deleted from the "exists_phone" 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 - 02:40:03 CEST