Database design question

[Database Administrator]

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
sim_id

PHONES
phone_id
phone_sim_id references sims(sim_id)


### Method 2

PHONES
phone_id

SIMS
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.

[Database Administrator]

I think that you need a third table for 3NF called sim_phone or
similar

Foriegn keys to the sim and phone PKs, maybe an In date and Out date
and other attributes as required. Contraint is either the two FK, or
the FKs and date.

HTH,

Tim

On Sep 30, 9:43*am, "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
> *sim_id
>
> PHONES
> *phone_id
> *phone_sim_id references sims(sim_id)
>
> ### Method 2
>
> PHONES
> *phone_id
>
> SIMS
> *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.

[Database Administrator]

>
> On Sep 30, 9:43 am, "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
>> sim_id
>>
>> PHONES
>> phone_id
>> phone_sim_id references sims(sim_id)
>>
>> ### Method 2
>>
>> PHONES
>> phone_id
>>
>> SIMS
>> 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.
>


Tim Mills-Groninger wrote:
> I think that you need a third table for 3NF called sim_phone or
> similar
>
> Foriegn keys to the sim and phone PKs, maybe an In date and Out date
> and other attributes as required. Contraint is either the two FK, or
> the FKs and date.
>
> HTH,
>
> Tim


Thanks for your response Tim.

I am considering that yes. This would also allow me to stop using NULL
to represent non-association.

Currently though, I have the following (and this fulfills all the rules) :-

PHONE
phone_id (PK)
phone_sim_id (FK SIM(sim_id) UNIQUE NULL)

SIM
sim_id (PK)

This satisfies the following rules:

1) A phone can be not-associated with a sim-card
2) A phone can be associated with ONE sim-card
3) A sim-card cannot be associated with more than one phone

The "unique" modifier stops more than one phone being associated with a
particular sim, and the fact that phone_sim_id can be null allows a
phone not to be associated with any sim.

If I wanted to have something against NULL, which I may, then this might
be better (as you suggested above) : -

PHONE
phone_id (PK)

SIM
sim_id (PK)

PHONE_SIM
phone_id
sim_id

INDEX ON PHONE_SIM (phone_id, sim_id)

Thoughts anyone? A better way?

Thanks,

Mark.
--

[Database Administrator]

On Sep 30, 3:30*pm, "Mark S. (UK)" wrote:
> > On Sep 30, 9:43 am, "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. *Simpleenough.
>
> >> 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
> >> *sim_id
>
> >> PHONES
> >> *phone_id
> >> *phone_sim_id references sims(sim_id)
>
> >> ### Method 2
>
> >> PHONES
> >> *phone_id
>
> >> SIMS
> >> *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.
>
> Tim Mills-Groninger wrote:
> > I think that you need a third table for 3NF called sim_phone or
> > similar
>
> > Foriegn keys to the sim and phone PKs, maybe an In date and Out date
> > and other attributes as required. *Contraint is either the two FK, or
> > the FKs and date.
>
> > HTH,
>
> > Tim
>
> Thanks for your response Tim.
>
> I am considering that yes. *This would also allow me to stop using NULL
> to represent non-association.
>
> Currently though, I have the following (and this fulfills all the rules) :-
>
> PHONE
> * * phone_id (PK)
> * * phone_sim_id (FK SIM(sim_id) UNIQUE NULL)
>
> SIM
> * * sim_id (PK)
>
> This satisfies the following rules:
>
> 1) A phone can be not-associated with a sim-card
> 2) A phone can be associated with ONE sim-card
> 3) A sim-card cannot be associated with more than one phone
>
> The "unique" modifier stops more than one phone being associated with a
> particular sim, and the fact that phone_sim_id can be null allows a
> phone not to be associated with any sim.
>
> If I wanted to have something against NULL, which I may, then this might
> be better (as you suggested above) : -
>
> PHONE
> * * phone_id (PK)
>
> SIM
> * * sim_id (PK)
>
> PHONE_SIM
> * * phone_id
> * * sim_id
>
> INDEX ON PHONE_SIM (phone_id, sim_id)
>
> Thoughts anyone? *A better way?
>
> Thanks,
>
> Mark.
> --

The three table model is my preference generally, but, as you note
this one boils down to a little bit of style. If you think about the
reality you are modeling, then the fact that the phone has a SIM slot
makes your preference seem better. So I think I would support your
current design.

HTH, ed

[Database Administrator]

On Oct 1, 2:21*am, Ed Prochak wrote:
> On Sep 30, 3:30*pm, "Mark S. (UK)" wrote:
>
>
>
> > > On Sep 30, 9:43 am, "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
> > >> *sim_id
>
> > >> PHONES
> > >> *phone_id
> > >> *phone_sim_id references sims(sim_id)
>
> > >> ### Method 2
>
> > >> PHONES
> > >> *phone_id
>
> > >> SIMS
> > >> *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.
>
> > Tim Mills-Groninger wrote:
> > > I think that you need a third table for 3NF called sim_phone or
> > > similar
>
> > > Foriegn keys to the sim and phone PKs, maybe an In date and Out date
> > > and other attributes as required. *Contraint is either the two FK, or
> > > the FKs and date.
>
> > > HTH,
>
> > > Tim
>
> > Thanks for your response Tim.
>
> > I am considering that yes. *This would also allow me to stop using NULL
> > to represent non-association.
>
> > Currently though, I have the following (and this fulfills all the rules) :-
>
> > PHONE
> > * * phone_id (PK)
> > * * phone_sim_id (FK SIM(sim_id) UNIQUE NULL)
>
> > SIM
> > * * sim_id (PK)
>
> > This satisfies the following rules:
>
> > 1) A phone can be not-associated with a sim-card
> > 2) A phone can be associated with ONE sim-card
> > 3) A sim-card cannot be associated with more than one phone
>
> > The "unique" modifier stops more than one phone being associated with a
> > particular sim, and the fact that phone_sim_id can be null allows a
> > phone not to be associated with any sim.
>
> > If I wanted to have something against NULL, which I may, then this might
> > be better (as you suggested above) : -
>
> > PHONE
> > * * phone_id (PK)
>
> > SIM
> > * * sim_id (PK)
>
> > PHONE_SIM
> > * * phone_id
> > * * sim_id
>
> > INDEX ON PHONE_SIM (phone_id, sim_id)
>
> > Thoughts anyone? *A better way?
>
> > Thanks,
>
> > Mark.
> > --
>
> The three table model is my preference generally, but, as you note
> this one boils down to a little bit of style. If you think about the
> reality you are modeling, then *the fact that the phone has a SIM slot
> makes your preference seem better. So I think I would support your
> current design.
>
> HTH, *ed

Hi,
Even i also support the existing design. Because if you consider the
same situation in the class structure, then you have phone with sim
(usually), but this is not a case that you sim (with Phone?
exceptional). Means sim would be your parent class and you can
override the features in phone class. So definitely its a better
design considering REAL Use Case instead of creating a third table
(moreover we use to say that normally will create 3rd table when you
have many-to-many relation)
~OM

[Database Administrator]

Mark,

NULL and UNIQUE can be problematic. For example SQL Server 2005
accepts just one NULL entry in a unique column.

> Thoughts anyone? *A better way? <

You could let the keys overlap to enforce your rules:

CREATE TABLE Phones_SIMs
(phone_id INT NOT NULL UNIQUE -- just one sim card by phone
REFERENCES Phones(phone_id),
sim_id INT NOT NULL UNIQUE -- just one phone by sim card
REFERENCES SIMs(sim_id),
PRIMARY KEY(phone_id, sim_id));

Brgds

Philipp Post

[Database Administrator]

"Mark S. (UK)" wrote in message
news:w_mdndd4V-qQpn_VnZ2dnUVZ8sednZ2d@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
> sim_id
>
> PHONES
> phone_id
> phone_sim_id references sims(sim_id)
>
>
> ### Method 2
>
> PHONES
> phone_id
>
> SIMS
> 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.

If it was me, I'd go with a third table, with just two columns: (sim_id,
phone_id). I'd add two UNIQUE constraints to prevent a sim_id from
appearing more than once in the table, and to likewise prevent a phone_id
from appearing more than once in the table. I would prepare for the chance
that this table might get more columns in the future, if attributes of the
sim-to-phone relationship are discovered (things like "date installed").

I can't back this up from a theory point of view, but it works. And the
day that somebody invents a phone with more than one space for a sim card,
or a sim card that can be "in" more than one phone at a time, this design
is going to look great.

[Database Administrator]

On 2008-09-30, Mark S. (UK) wrote:

> 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.

I'm talking real-world here:
some phones can have more than one sim card (special adaptors are used)


> ### Method 1
>
> SIMS
> sim_id
>
> PHONES
> phone_id
> phone_sim_id references sims(sim_id)

you could add a UNIQUE constraint to the phone_sim_id column
that would ensure that only one phone had a certain sim, because of the
way NULL behaves this constraint _will_ allow several phones to have no sim.


> ### Method 2
>
> PHONES
> phone_id
>
> SIMS
> sim_id
> sim_phone_id references phones(phone_id)

again you could use unique here to enforce your rules.

> Problem number 2 is, using either method above can break the rules and
> create an impossible situation.

Use unique, or use method 2 for my version of how the world is.

Bye.
Jasen

[Database Administrator]

Jasen Betts wrote:
> On 2008-09-30, Mark S. (UK) wrote:
>
>> 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.
>
> I'm talking real-world here:
> some phones can have more than one sim card (special adaptors are used)
>
>
>> ### Method 1
>>
>> SIMS
>> sim_id
>>
>> PHONES
>> phone_id
>> phone_sim_id references sims(sim_id)
>
> you could add a UNIQUE constraint to the phone_sim_id column
> that would ensure that only one phone had a certain sim, because of the
> way NULL behaves this constraint _will_ allow several phones to have no sim.
>
>
>> ### Method 2
>>
>> PHONES
>> phone_id
>>
>> SIMS
>> sim_id
>> sim_phone_id references phones(phone_id)
>
> again you could use unique here to enforce your rules.
>
>> Problem number 2 is, using either method above can break the rules and
>> create an impossible situation.
>
> Use unique, or use method 2 for my version of how the world is.
>
> Bye.
> Jasen
>


My scenario isn't exactly as described; it is not actually phones I'm
dealing with; they are devices that have sim cards put inside and before
being screwed together and sent out. Sim cards are only ever changed by
engineers. I used phones to provide a more familiar scenario.

Thanks for your help and ideas everyone :-)

Mark.
--

[Database Administrator]

On Wed, 1 Oct 2008 00:37:47 -0700 (PDT), oakulkarni
wrote in
:

>Even i also support the existing design. Because if you consider the
>same situation in the class structure, then you have phone with sim
>(usually), but this is not a case that you sim (with Phone?
>exceptional). Means sim would be your parent class and you can
>override the features in phone class.

Sorry, but I must disagree. This is a "has a" relationship, not an
"is a" relationship. The proper OO design would be composition, not
inheritance.

>So definitely its a better design considering REAL Use Case instead
>of creating a third table (moreover we use to say that normally
>will create 3rd table when you have many-to-many relation)

I agree with your conclusion, though not your analogy. Since a phone
may have zero or one sims and the presence or absence of the sim is
directly dependent on the phone, putting the sim's id in a column in
the phones table:

CREATE TABLE phones (
phone_id INT PRIMARY KEY,
sim_id INT NULL,
CONSTRAINT FK_phones_sims FOREIGN KEY
(sim_id) REFERENCES sims (sim_id)
);

would not violate the 3NF.
--
Charles Calvert | Web-site Design/Development
Celtic Wolf, Inc. | Software Design/Development
http://www.celticwolf.com/ | Data Conversion
(703) 580-0210 | Project Management