Simple database design question

On Oct 3, 12:56 pm, xyzzy wrote:
> On Oct 2, 5:09 am, -CELKO- 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).

xyzzy wrote:
> On Oct 2, 5:09 am, -CELKO- 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?

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