Simple database design question

[Mark S. (UK)]

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.

[Bob Badour]

Mark S. (UK) wrote:

Neither of the above handle both 0 cases.

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

[Mark S. (UK)]

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

>> 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:
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]

Mark S. (UK) wrote:

Yes, you are missing a healthy aversion to NULL.

Only one (compound) candidate key?

[ma...@alienmuppet.com]

I have thought of another solutions where the primary key of the sim
table is also a foreign key that relates to the phone table : -

PHONE
phone_id (PK)

SIM
sim_id (PK, FK PHONE(phone_id))

Unfortunately though that stops the sim card being able to exist
without being related to a phone, since the primary key must not be
null.

The whole idea is that sim cards and phones can be bought and added to
the database. At some point, the sim can be associated to ONE phone
(and that ONE phone can only be associated with that ONE sim). The
phone cannot have many sims, and the sims cannot have many phones.

Method 1 is working at the moment:

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

SIM
sim_id (PK)

This is ensuring the integrity of all the rules I've mentioned,
however from what you've said I feel I may be missing something, and
may well kick myself once I figure out what it is....

Mark.
--

[ma...@alienmuppet.com]

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

[Mark S. (UK)]

Those two posts from "ma...@alienmuppet.com" are from me Bob. I had to
switch to using google groups to send posts as my ISP's news service
went down temporarily.

Mark.

[Bob Badour]

ma...@alienmuppet.com wrote:

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?

[paul c]

ma...@alienmuppet.com wrote:
...

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

PMFJI, but don't you mean two unique indexes for RELATION, one on
phone_id and one on sim_id? (I gather that the typical sql dbms
doesn't support candidate keys, so one must use physical features if one
wants to minimize the number of tables.)

[JOG]

On Sep 30, 4:07 pm, "Mark S. (UK)" <ma...@nospamhere.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.

Here you have described two unary predicates: "exists_sim(sim)" and
"exists_phone(phone)".
Congratulations you have identified you're first two relations.

>
> Rule 1: A sim card can either be in a phone, or not in a phone.

Here you have identified the binary predicate: "contains(phone, sim)"
This is you're second relation. I think we'd agree the items in this
relation must exist... great, we have relations that state that
already, so two foreign key constraints linking to them are required.

> It cannot be in more than one phone.

Here you have identified a first uniqueness constraint on the
"contains" relation.

> Rule 2: A phone can either have one sim inserted, or no sim inserted.

And there's your second uniqueness constraint on the "contains"
relation. Implement them and job done - and with not a null in sight.
Breaking your problems down into predicates in this fashion is always
the way forward

Now tell me what would happen if a phone was deleted from the
"exists_phone" table?

[paul c]

JOG wrote:
...

> Now tell me what would happen if a phone was deleted from the
> "exists_phone" table?

> ...

Heh, I imagine some OO pgmr would need to write a new method, but I
don't know that for sure, thank goodness.

[David BL]

On Oct 1, 8:40 am, JOG <j...@cs.nott.ac.uk> wrote:
> On Sep 30, 4:07 pm, "Mark S. (UK)" <ma...@nospamhere.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.
>
> Here you have described two unary predicates: "exists_sim(sim)" and
> "exists_phone(phone)".

Just a rather off topic comment... One time on this ng I discussed a
unary predicate like this that stated that such and such exists, and
was beaten over the head for being metaphysical. However I don’t
think there is anything wrong with using the term "exists" as long as
it is understood that the external predicate is in fact something more
specific – such as

exists_phone(P) :- phone P was manufactured by Acme Corporation.

[-CELKO-]

I'd bet that you don't call the industry identifier a "sim_id" -- is
it a sim_nbr or something?? Ditto for a phone number; I think that is
15 digits in the CITT standard. Learn to use the right names for
things, so you can have data interchange, a data dictionary, etc.

CREATE TABLE Sims
(sim_nbr CHAR(n) NOT NULL PRIMARY KEY,
..);

CREATE TABLE Phones
(phone_nbr CHAR(15) NOT NULL PRIMARY KEY,
..);

Put the sims in the phones ..

CREATE TABLE PhoneSimAssignments
(sim_nbr CHAR(n) NOT NULL UNIQUE -- industry standards?
REFERENCES Sims(sim_nbr)
ON DELETE CASCADE,
phone_nbr CHAR(15) NOT NULL UNIQUE
REFERENCES Phones(phone_nbr)
ON DELETE CASCADE,
..);

Use a view to show the sims status of all phones

CREATE VIEW PhoneAssignments (..)
AS
SELECT sim_nbr, phone_nbr, ..
FROM Phones AS P
LEFT OUTER JOIN
PhoneAssignments AS A
ON A.phone_nbr = P.phone_nbr;

[Bob Badour]

JOG wrote:

> On Sep 30, 4:07 pm, "Mark S. (UK)" <ma...@nospamhere.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.
>
>
> Here you have described two unary predicates: "exists_sim(sim)" and
> "exists_phone(phone)".
> Congratulations you have identified you're first two relations.
>
>
>>Rule 1: A sim card can either be in a phone, or not in a phone.
>
>
> Here you have identified the binary predicate: "contains(phone, sim)"
> This is you're second relation. I think we'd agree the items in this
> relation must exist... great, we have relations that state that
> already, so two foreign key constraints linking to them are required.
>
>
>>It cannot be in more than one phone.
>
>
> Here you have identified a first uniqueness constraint on the
> "contains" relation.
>
>
>>Rule 2: A phone can either have one sim inserted, or no sim inserted.
>
>
> And there's your second uniqueness constraint on the "contains"
> relation. Implement them and job done - and with not a null in sight.
> Breaking your problems down into predicates in this fashion is always
> the way forward
>
> Now tell me what would happen if a phone was deleted from the
> "exists_phone" table?

Perhaps more urgently, what would happen if a sim was deleted from the
SIMS table?

[xyzzy]

This ticks all the boxes as far as I can see. The original
requirement was for only 1 SIM associated with a phone.
This requirement might not be correct -- even if he is selling phones
with one SIM & phone per box.
You can get phones that use more than one SIM, eg. Cect HT-508 dual
SIM mobile phone.

What if you can have 0, 1, or 2 SIMs associated with a phone?

[xyzzy]

S'pose you could add slot_number to PhoneAssignments and make it not
null, check slot_number in (1, 2).

[Mark S. (UK)]

That can never happen in the scenario I am dealing with. They are not
actually phones; they are devices that take sim cards, and those sim
cards are put inside the devices and screwed shut before being sent out.
Only engineers belonging to the company should have access to them,
and they'd only be changed upon failure. I just wanted to provide a
more familiar scenario in my original question.

Thanks for your ideas and insight everyone, you've been very helpful.

Mark.
--

[patri...@yahoo.com]

He's probably working for Apple.