Database design question

#1

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.

#2

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)" <ma... (AT) nospamhere (DOT) com> wrote:

Quote:

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.

#3

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)" <ma... (AT) nospamhere (DOT) com> wrote:

Quote:

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.

#4

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)" <ma... (AT) nospamhere (DOT) com> wrote:

Quote:

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.

#5

Quote:

On Sep 30, 9:43 am, "Mark S. (UK)" <ma... (AT) nospamhere (DOT) com> 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:

Quote:

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

#6

Quote:

On Sep 30, 9:43 am, "Mark S. (UK)" <ma... (AT) nospamhere (DOT) com> 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:

Quote:

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

#7

Quote:

On Sep 30, 9:43 am, "Mark S. (UK)" <ma... (AT) nospamhere (DOT) com> 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:

Quote:

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

#8

On Sep 30, 3:30*pm, "Mark S. (UK)" <ma... (AT) nospamhere (DOT) com> wrote:

Quote:

On Sep 30, 9:43 am, "Mark S. (UK)" <ma... (AT) nospamhere (DOT) com> 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

#9

On Sep 30, 3:30*pm, "Mark S. (UK)" <ma... (AT) nospamhere (DOT) com> wrote:

Quote:

On Sep 30, 9:43 am, "Mark S. (UK)" <ma... (AT) nospamhere (DOT) com> 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

#10

On Sep 30, 3:30*pm, "Mark S. (UK)" <ma... (AT) nospamhere (DOT) com> wrote:

Quote:

On Sep 30, 9:43 am, "Mark S. (UK)" <ma... (AT) nospamhere (DOT) com> 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