Re: Simple database design question
Date: Tue, 30 Sep 2008 17:03:31 -0300
Message-ID: <48e28616$0$5503$9a566e8b_at_news.aliant.net>
Mark S. (UK) wrote:
> 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?
Yes, you are missing a healthy aversion to NULL.
>>>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?
Only one (compound) candidate key? Received on Tue Sep 30 2008 - 22:03:31 CEST