Re: Simple database design question

On Oct 3, 12:56 pm, xyzzy <goo..._at_m-streeter.demon.co.uk> wrote:
> 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?

S'pose you could add slot_number to PhoneAssignments and make it not null, check slot_number in (1, 2). Received on Fri Oct 03 2008 - 05:51:13 CEST