Re: Simple database design question
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 30 Sep 2008 19:02:50 -0300
Message-ID: <48e2a20e$0$5492$9a566e8b_at_news.aliant.net>
>
>
>
>
> I had the feeling you were going to mention that :-) I have been
> wrestling with myself on that. For years I had an aversion to NULL.
> ALL of my fields used to say NOT NULL next to them in my definitions,
> however it seemed a useful way of saying "this object is not yet
> associated" ...
>
> As far as I'm aware, there are two alternatives; 1) having a dummy
> object that unassociated objects are related to... OR having a
> separate relation table to associate the objects, and without an entry
> in that relation table, there is no relation. That way, I could get
> rid of using NULL.
>
> Most of my fields are at least still NOT NULL.
>
>
>
>
> Sorry, I have missed out some important information:
>
> PHONE:
> phone_id (PK)
>
> SIM:
> sim_id (PK)
>
> RELATION:
> phone_id
> sim_id
>
> unique index on phone_id, sim_id
>
> So now I've got a couple of primary keys as well as the compound
> index..
>
> Mark.
> --
Date: Tue, 30 Sep 2008 19:02:50 -0300
Message-ID: <48e2a20e$0$5492$9a566e8b_at_news.aliant.net>
marks_at_alienmuppet.com wrote:
> On Sep 30, 9:03 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
>>Mark S. (UK) wrote: >> >>>Bob Badour wrote: >> >>>>Mark S. (UK) wrote: >> [Quoted] >>>>>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. >> [Quoted] >>>>>Rule 1: A sim card can either be in a phone, or not in a phone. It [Quoted] >>>>>cannot be in more than one phone. >> [Quoted] >>>>>Rule 2: A phone can either have one sim inserted, or no sim inserted. >> [Quoted] >>>>>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.
>
>
>
>
> I had the feeling you were going to mention that :-) I have been
> wrestling with myself on that. For years I had an aversion to NULL.
> ALL of my fields used to say NOT NULL next to them in my definitions,
> however it seemed a useful way of saying "this object is not yet
> associated" ...
>
> As far as I'm aware, there are two alternatives; 1) having a dummy
> object that unassociated objects are related to... OR having a
> separate relation table to associate the objects, and without an entry
> in that relation table, there is no relation. That way, I could get
> rid of using NULL.
>
> Most of my fields are at least still NOT 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: [Quoted] >>> 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?
>
>
> Sorry, I have missed out some important information:
>
> PHONE:
> phone_id (PK)
>
> SIM:
> sim_id (PK)
>
> RELATION:
> phone_id
> sim_id
>
> unique index on phone_id, sim_id
>
> So now I've got a couple of primary keys as well as the compound
> index..
>
> Mark.
> --
I suggest you reconsider your constraints. Can a sim be in two phones? Can a phone contain two sims? Does the index you created enforce your requirements? What are the candidate keys of RELATION? Received on Wed Oct 01 2008 - 00:02:50 CEST