I am not sure if this is possible but is there a way that I can have
multiple columns from different tables be a foreign key to a single column
in another table, or do I need to write a check function and if so how could
I set up a relation?
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.
