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







All Articles