Re: Simple database design question

On Oct 2, 5:09 am, -CELKO- <jcelko..._at_earthlink.net> wrote:
> 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;

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? Received on Fri Oct 03 2008 - 04:56:43 CEST