SQL package to pipe relationship
I'm having a problem figuring out the many-to-many relationship between a package and a channel. Each channel belongs to a packet, but more than 1 packet at a time.
IE: Channel 1 refers to packet 1,2,3,4,5 Channel 2 refers to packet 2.5
So, etc. How do I write tables to create these relationships? So far I have:
CREATE TABLE Channel
(
ChannelID NUMBER(10) CONSTRAINT PK_Channel PRIMARY KEY,
ChannelName VARCHAR2(12) NOT NULL,
ChannelDesc VARCHAR2(20) NOT NULL,
ChannelNumber NUMBER(3) NOT NULL,
SupID NUMBER(2) CONSTRAINT FK_Channel_Supplier REFERENCES Supplier (SupID),
PackageID NUMBER(5) CONSTRAINT FK_Channel_Package REFERENCES Package (PackageID)
);
INSERT INTO CHANNEL
VALUES(0001, 'Channel 1', '1st Channel', 01, 10001);
INSERT INTO CHANNEL
VALUES(0002,'Channel 2', '2nd Channel', 02, 10001);
INSERT INTO CHANNEL
VALUES(0003, 'Channel 3', '3rd Channel', 03, 10002);
INSERT INTO CHANNEL
VALUES(0004, 'Channel 4', '4th Channel', 04, 10003);
INSERT INTO CHANNEL
VALUES(0005, 'Channel 5', '5th Channel', 05, 10004);
INSERT INTO CHANNEL
VALUES(0006, 'Channel 6', '6th Channel', 06, 10001);
INSERT INTO CHANNEL
VALUES(0007, 'Channel 7', '7th Channel', 07, 10004);
INSERT INTO CHANNEL
VALUES(0008, 'Channel 8', '8th Channel', 08, 20005);
CREATE TABLE Package
(
PackageID NUMBER(10) CONSTRAINT PK_Package PRIMARY KEY,
PackageDescrip VARCHAR2(25) NOT NULL,
PackageFee NUMBER(3) DEFAULT '0',
);
INSERT INTO PACKAGE
VALUES(01, 'Movies Galore', 30);
INSERT INTO PACKAGE
VALUES(02, 'News Globe', 30);
INSERT INTO PACKAGE
VALUES(03, 'Total Watcher', 40);
INSERT INTO PACKAGE
VALUES(04, 'Couch Potato', 50);
+3
source to share
1 answer
I would add an additional table to handle the channel to package relationship and pull out the PackageID column in the Channel table.
CREATE TABLE PackageChannel
(
PackageChannelID NUMBER(10) CONSTRAINT PK_PackageChannel PRIMARY KEY,
PackageID NUMBER(10),
ChannelID NUMBER(10)
);
Then process the relation in the select statement
SELECT PackageID FROM PackageChannel WHERE ChannelID = 0001;
0
source to share