Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

AUTHORS
---------------------------
ID    BOOK_ID    JOURNAL_ID    NAME
0     54         212           A.R Smith
1     598        NULL          J.R. Hartley
share|improve this question

2 Answers 2

up vote 5 down vote accepted

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 ║
╚════════╩══════════╝
share|improve this answer
    
Thanks Leigh - I like your answer too (+1) - This is a good illustration of the schema subtype idea applied to OP's situation. –  Joel Brown Apr 27 '12 at 16:55

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.

share|improve this answer
    
I'm looking at Oracle Objects/Types - could this be the way to go? –  Vidar Apr 27 '12 at 14:25
1  
@Vidar - 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 supertype and sub-type, it is from a data modeling perspective Leigh Riffel has a good answer that illustrates what my answer is suggesting. –  Joel Brown Apr 27 '12 at 16:53
    
thanks very much for your help. –  Vidar May 11 '12 at 12:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.