I have two tables, BOOK and JOURNAL. I want to store their authors in an intersection table called AUTHORS. Is it acceptable practice to have mulitple reference id columns (kind of FK's I think) to other tables i.e. BOOK and JOURNAL? This is to save having separate author tables for Books and Journals.
So, AUTHORS would look like:
| ID | BOOK_ID | JOURNAL_ID | NAME |
|---|---|---|---|
| 0 | 54 | 212 | A.R Smith |
| 1 | 598 | NULL | J.R. Hartley |
This is a possible design based on Joel Brown's correct answer (+1). The WorkIds could come from the same sequence.
Author Table
╔══════════╦═══════════╦══════════╗
║ AuthorID ║ FirstName ║ LastName ║
╠══════════╬═══════════╬══════════╣
║ 0 ║ A.R ║ Smith ║
║ 1 ║ J.R. ║ Hartley ║
╚══════════╩═══════════╩══════════╝
AuthorWorks Table
╔══════════╦════════╗
║ AuthorId ║ WorkId ║
╠══════════╬════════╣
║ 0 ║ 1 ║
║ 0 ║ 2 ║
║ 1 ║ 3 ║
║ 0 ║ 4 ║
╚══════════╩════════╝
Book Table
╔════════╦══════════╗
║ WorkId ║ Title ║
╠════════╬══════════╣
║ 1 ║ asdf ║
║ 3 ║ jkl ║
║ 4 ║ zxcvzxcv ║
╚════════╩══════════╝
Journal Table
╔════════╦══════════╗
║ WorkId ║ Title ║
╠════════╬══════════╣
║ 2 ║ dfghdfgh ║
╚════════╩══════════╝
What would be more conventional is to have an AUTHOR table that lists the names of each author once, along with any other information you may eventually need, and then have your intersection table reference AUTHOR and then either BOOK or JOURNAL. A lot of people would have a different intersection table for each type of writing, or at least create a supertype above BOOK and JOURNAL that encompasses both. Your single intersection table could then reference the supertype.
Most people would not have a dual-purpose intersection table, but I've seen it done. What doesn't make sense is your first example row, where A.R Smith authors one book and one journal in the same record. Each record in the intersection table should denote a single relationship otherwise you are exposing yourself to deletion anomalies.
Regarding using Oracle Objects/Types;I wouldn't rule out Oracle Object Type, but it is really more of a programming tool rather than a database schema tool, insofar as it incorporates program logic (e.g. PL/SQL) as well as data structure. When I use the terms super-type and sub-type, it is from a data modeling perspective. Leigh Riffel has a good answer that illustrates what my answer is suggesting.
