Database design question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark S. (UK) - 30 Sep 2008 15:43 GMT

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.

Reply to this Message

Tim Mills-Groninger - 30 Sep 2008 20:40 GMT

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

> Hi all,
> I have 2 tables, "sims" (as in mobile phone sim cards) and "phones" (as
[quoted text clipped - 48 lines]
>
> Mark.

Reply to this Message

Mark S. (UK) - 30 Sep 2008 21:30 GMT

>> Hi all,
>> I have 2 tables, "sims" (as in mobile phone sim cards) and "phones" (as
[quoted text clipped - 48 lines]
>>
>> Mark.

> I think that you need a third table for 3NF called sim_phone or
> similar
[quoted text clipped - 6 lines]
>
> 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.
--

Reply to this Message

Ed Prochak - 30 Sep 2008 22:21 GMT

> >> Hi all,
> >> I have 2 tables, "sims" (as in mobile phone sim cards) and "phones" (as
[quoted text clipped - 105 lines]
> 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

Reply to this Message

oakulkarni - 01 Oct 2008 08:37 GMT

> > >> Hi all,
> > >> I have 2 tables, "sims" (as in mobile phone sim cards) and "phones" (as
[quoted text clipped - 113 lines]
>
> 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

Reply to this Message

Charles Calvert - 01 Nov 2008 03:18 GMT

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

Signature

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

Reply to this Message

Jasen Betts - 02 Nov 2008 02:00 GMT

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

allowing nulls violates 3NF

Bye.
Jasen

Reply to this Message

Gene Wirchenko - 04 Nov 2008 04:38 GMT

>> CREATE TABLE phones (
>> phone_id INT PRIMARY KEY,
[quoted text clipped - 6 lines]
>
>allowing nulls violates 3NF

^^^
ITYM "1NF".

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Reply to this Message

Philipp Post - 01 Oct 2008 11:21 GMT

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

Reply to this Message

Walter Mitty - 01 Oct 2008 12:28 GMT

> Hi all,
> I have 2 tables, "sims" (as in mobile phone sim cards) and "phones" (as
[quoted text clipped - 49 lines]
>
> 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.

Reply to this Message

Jasen Betts - 02 Oct 2008 09:04 GMT

> 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
>
[quoted text clipped - 4 lines]
> 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
>
[quoted text clipped - 4 lines]
> 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

Reply to this Message

Mark S. (UK) - 07 Oct 2008 11:34 GMT

>> 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"
[quoted text clipped - 39 lines]
> 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.
--

Reply to this Message