Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Message-ID: <CtmdncWgM5g03X_VnZ2dnUVZ8trinZ2d (AT) pipex (DOT) net>
Lines: 53
X-Usenet-Provider: http://www.giganews.com
NNTP-Posting-Host: 84.12.11.241
X-Trace: sv3-t1iKYIFQX2noV2PHZLpU9xaqiEmLzL/2pMp9y0Mzxw8dIcit1HzviY+YnSLPX9tFymEiRdC1SGCEbtE!n YFf3799zifNQaSsCTg/vmYFAsNHgHnbVCdrIFihJjk+5qgvwmQCMuWr7qqF0luFKESrwO WE2Rsv!4oqHftBazT+nh19YbHxXUg==
X-Complaints-To: abuse (AT) dsl (DOT) pipex.net
X-DMCA-Complaints-To: abuse (AT) dsl (DOT) pipex.net
X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly
X-Postfilter: 1.3.39
Bytes: 2610
Xref: number1.nntp.dca.giganews.com comp.databases.theory:68083

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.

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.

Mark S. (UK) wrote:

Neither of the above handle both 0 cases.


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.

Mark S. (UK) wrote:

Neither of the above handle both 0 cases.


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.

Mark S. (UK) wrote:

Neither of the above handle both 0 cases.


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.

Mark S. (UK) wrote:

Neither of the above handle both 0 cases.


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.

Mark S. (UK) wrote:

Neither of the above handle both 0 cases.


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.

Mark S. (UK) wrote:

Neither of the above handle both 0 cases.


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.

Mark S. (UK) wrote:

Neither of the above handle both 0 cases.


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.

Bob Badour wrote:
In method 1, if phone_sim_id is NULL, then is it not related to 0
records? Or am I missing something?

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?

Thanks,

Mark.
--

Bob Badour wrote:
In method 1, if phone_sim_id is NULL, then is it not related to 0
records? Or am I missing something?

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?

Thanks,

Mark.
--