Re: Simple database design question

From: anders t <anthu_001_at_no_-_spam_.hotmail.com>
Date: Mon, 29 Jun 2009 22:43:59 +0200
Message-ID: <ja9i459fb3tc23dqa93pk4rit5uvv308sa_at_4ax.com>


Quoting -CELKO- in comp.databases.theory:
>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,
>..);

Please forgive me for beating this dead horse, but I see no PRIMARY KEY assignment here (in PhoneSimAssignments). Is it accepted practise not to assign a PK in a case like this? Anyways, if a PK is assigned then it cannot be composite, since that would violate the rule of minimality of the PK, right? Actually, isn't it so that PhoneSimAssignments is the result of multiple inheritance (SimWithPhone & PhoneWithSim)?

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

-- 
       MANCHESTER UNITED FC
            CHAMPIONS 
        ENGLAND & THE WORLD
Received on Mon Jun 29 2009 - 22:43:59 CEST

Original text of this message