Simple database design question
Date: Tue, 30 Sep 2008 16:07:20 +0100
Message-ID: <CtmdncWgM5g03X_VnZ2dnUVZ8trinZ2d_at_pipex.net>
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_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 Tue Sep 30 2008 - 17:07:20 CEST