CREATE TABLE seriestitle (
seriestitle text
);
CREATE TABLE compilationtitle (
compilationtitle text PRIMARY KEY,
pubddate text NOT NULL,
isbn text NOT NULL,
style text REFERENCES style,
storyline text REFERENCES storyline(storyline) DEFAULT '_default_',
seriestitle text REFERENCES seriestitle DEFAULT '_default_',
price text NOT NULL,
);
CREATE TABLE storytitle (
storytitle text PRIMARY KEY,
notes text DEFAULT '_default_',
);
CREATE TABLE issuetitle (
issuetitle text PRIMARY KEY,
pubdate text NOT NULL,
price text NOT NULL,
bookcover OID REFERENCES bookcover(bookcover),
compilationtitle text REFERENCES compilation(compilation) DEFAULT '_default_',
seriestitle text REFERENCES seriestitle DEFAULT '_default_',
);
CREATE TABLE noveltitle (
noveltitle text NOT NULL,
isbn text NOT NULL,
pubdate text NOT NULL,
price text NOT NULL,
bookcover OID REFERENCES bookcover(bookcover),
seriestitle text REFERENCES seriestitle DEFAULT '_default_',
);
The seriestitle table will contain a list of all the series names that I am collecting but I want to be able to relate them to the issuetitle, compilationtitle, and noveltitle tables. My thoughts were using a foreign key to do this. Create an attribute in seriestitle called booktitle and have that be referenced from the other three but that doesn't seem possible or at least I couldn't find out how in the documentation. If I were to create three separate attributes for each of the separate titles in the seriestitle table then reference those attributes from their respective tables that would produce errors I believe, because a foreign key can't be null and not every attribute will have a value in every tuple. In reading about normalization a single attribute cannot contain multiple values. For example:
INSERT INTO issuetitle (seriestitle)
VALUES ('batman, catwoman')
;
Now the seriestitle table would contain 'batman, catwoman' for a value but that would break normalization rules
The only thing left that I can think of is to create some sort of a function that checks to see whether the value being entered into noveltitle, issuetitle, or compilationtitle is contained within seriestitle but then how would I go about relating a row from one of those three tables to a row in seriestable from a single attribute without having to do a lot of manual work?
Thanks.
