Re: Simple database design question
Date: Wed, 1 Oct 2008 12:09:11 -0700 (PDT)
Message-ID: <06eddaad-19e9-40f4-8609-daf1c7c86070_at_d1g2000hsg.googlegroups.com>
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
CREATE TABLE Phones
Put the sims in the phones ..
CREATE TABLE PhoneSimAssignments
REFERENCES Sims(sim_nbr)
(sim_nbr CHAR(n) NOT NULL PRIMARY KEY,
..);
(phone_nbr CHAR(15) NOT NULL PRIMARY KEY,
..);
(sim_nbr CHAR(n) NOT NULL UNIQUE -- industry standards?
ON DELETE CASCADE,
phone_nbr CHAR(15) NOT NULL UNIQUE
REFERENCES Phones(phone_nbr)
..);
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;Received on Wed Oct 01 2008 - 21:09:11 CEST