13 Replies Latest reply: Aug 24, 2012 9:15 AM by EdStevens RSS

    Multiple authors for a Book

    Pete_Sg1
      In the below example, the book titled 'Collapse of the Dollar' has multiple authors.

      create table books 
      (surrId number(5), 
      book_id number(7),
      isbn number (10), 
      title varchar2(100) , 
      Author varchar2(100)
      );
      
      
      insert into books values (1, 457, 8478, 'Perilous Power' , 'Noam Chomsky');
      insert into books values (2, 458, 2345, 'Macbeth' , 'Shakespeare');
      
      insert into books values (3, 459, 6789, 'Collapse of the Dollar' , 'James Turk');
      insert into books values (4, 459, 6789, 'Collapse of the Dollar' , 'John Rubino');
      
      col title format a35
      col author format a15
      col title format a15
      set lines 200
      
      SQL> select * from books;
      
          SURRID    BOOK_ID       ISBN TITLE                               AUTHOR
      ---------- ---------- ---------- ----------------------------------- ---------------
               1        457       8478 Perilous Power                      Noam Chomsky
               2        458       2345 Macbeth                             Shakespeare
               3        459       6789 Collapse of the Dollar              James Turk
               4        459       6789 Collapse of the Dollar              John Rubino
      I need to write a query whiich returns book details but it should identify records (ie. Titles) with multiple authors and return the record with Authors separated with pipe ('|') like (no need of SURRID column )

      expected output
            -- no need to retrieve surrogate ID
        BOOK_ID       ISBN TITLE                               AUTHOR
      --------- ---------- ----------------------------------- ---------------
            457       8478 Perilous Power                      Noam Chomsky
            458       2345 Macbeth                             Shakespeare
            459       6789 Collapse of the Dollar              James Turk|John Rubino 
            
      Related question on the above Table design (Create table DDL shown above):

      A table storing book details can only be designed like above. Right ? I mean, the duplication of records for one book because of multiple authors cannot be avoided. Right ?

      One wonders how Amazon has desined its books table :)
        • 1. Re: Multiple authors for a Book
          sybrand_b
          The design is simply incorrect.

          You have one entity book, one entity author, and a n:m relationshhip between those two entities.
          In a RDBMS this is implemented as a junction table.

          If you would correct the flawed design, you would also be able to write that query.

          I recommend
          drop table books purge
          at the earliest.

          You need this one table to be replaced by a table
          book
          author
          and
          bookauthor.
          Bookauthor has a foreign key to both book and author.


          Also table names need to be singular not plural

          -----------
          Sybrand Bakker
          Senior Oracle DBA

          Edited by: sybrand_b on 23-aug-2012 17:16
          • 2. Re: Multiple authors for a Book
            UW (Germany)
            If you have Oracle 11gR2 you can use the listagg function to get your desired result:
            select 
                book_id, 
                isbn, 
                title, 
                listagg(author,'|') within group (order by author) authors
            from books
            group by book_id, isbn, title;
            In general it would be a better idea to keep the authors in a separate table and to join the books table and the authors table on the book_id.

            Edited by: UW (Germany) on 23.08.2012 17:18
            • 3. Re: Multiple authors for a Book
              Solomon Yakobson
              select  book_id,
                      isbn,
                      title,
                      listagg(author,'|') within group(order by author) author
                from  books
                group by book_id,
                         isbn,
                         title
                order by book_id,
                         isbn,
                         title
              /
              
                 BOOK_ID       ISBN TITLE                          AUTHOR
              ---------- ---------- ------------------------------ ------------------------------
                     457       8478 Perilous Power                 Noam Chomsky
                     458       2345 Macbeth                        Shakespeare
                     459       6789 Collapse of the Dollar         James Turk|John Rubino
              
              SQL> 
              SY.
              • 4. Re: Multiple authors for a Book
                Solomon Yakobson
                And if you are not on 11.2:
                select  book_id,
                        isbn,
                        title,
                        rtrim(xmlagg(xmlelement(a,author,'|').extract('//text()') order by author),'|') author
                  from  books
                  group by book_id,
                           isbn,
                           title
                  order by book_id,
                           isbn,
                           title
                /
                
                   BOOK_ID       ISBN TITLE                          AUTHOR
                ---------- ---------- ------------------------------ ------------------------------
                       457       8478 Perilous Power                 Noam Chomsky
                       458       2345 Macbeth                        Shakespeare
                       459       6789 Collapse of the Dollar         James Turk|John Rubino
                
                SQL> 
                SY.
                • 5. Re: Multiple authors for a Book
                  Nicosa-Oracle
                  Hi,
                  Pete_Sg1 wrote:
                  I need to write a query whiich returns book details but it should identify records (ie. Titles) with multiple authors and return the record with Authors separated with pipe ('|') like (no need of SURRID column )
                  Get there : {message:id=9360005}
                  and scroll down to "string aggregation"
                  Pete_Sg1 wrote:
                  A table storing book details can only be designed like above. Right ? I mean, the duplication of records for one book because of multiple authors cannot be avoided. Right ?
                  I would have had a table for books, and a separate one for authors, and a 3rd one for their relations :
                  create table books
                  (
                  book_id integer,
                  title varchar2(100),
                  etc...
                  );
                  
                  create table authors
                  (
                  author_id integer,
                  name varchar2(100),
                  etc...
                  );
                  
                  create table book_authors
                  (
                  book_id integer,
                  author_id integer
                  );
                  You could also have only 2 tables book and authors (with authors table having a fk column to book_id).

                  It would depends if the author is supposed to remain unique throught different books.
                  - The 3 tables model would allow a single update on author information to be automatically "propagated" to all book he participed in.
                  - The 2 tables model would allow to have different information for each participation of the author to different books (but would certainly "duplicate" part of the data about the author)

                  One can also have even more tables to totally avoid data "duplication".
                  • 6. Re: Multiple authors for a Book
                    Carlovski
                    Sybrand,
                    >
                    Also table names need to be singular not plural
                    >
                    is your opinion, and preference rather than fact. And also one that I (And numerous others!) disagree on.

                    Carl
                    • 7. Re: Multiple authors for a Book
                      sybrand_b
                      It is not opinion.
                      It can be found in many instruction books.
                      It is sad you and many others don't understand the difference between an entity (the table) and it's ocurrences (the records).
                      Plural table names are just plain wrong.
                      But as many people talk of 'fields' when then mean 'columns', I'm not holding my breath they will ever learn.
                      The sad state of IT is a result of lack of people with proper formal tuition, not using any formal development procedures.
                      And yes, I am aware most people, incliuding you, have it wrong, and use incorrect terminology.
                      ---------
                      Sybrand Bakker
                      Senior Oracle DBA
                      • 8. Re: Multiple authors for a Book
                        EdStevens
                        sybrand_b wrote:
                        It is not opinion.
                        It can be found in many instruction books.
                        It is sad you and many others don't understand the difference between an entity (the table) and it's ocurrences (the records).
                        Plural table names are just plain wrong.
                        But as many people talk of 'fields' when then mean 'columns', I'm not holding my breath they will ever learn.
                        The sad state of IT is a result of lack of people with proper formal tuition, not using any formal development procedures.
                        And yes, I am aware most people, incliuding you, have it wrong, and use incorrect terminology.
                        ---------
                        Sybrand Bakker
                        Senior Oracle DBA
                        On list of priorities, I'd rank whether or not a table name is singular or pluarl as WAAAYYY down the list.

                        As an unrepentant COBOL programmer who cut his IT teeth on IBM S-360, I'm still a huge fan of extensive naming conventions. But I would be more concerned that the shop has A standard on the issue. Whether or not that standard specified singular or plural doesn't bother me - just as long as it is specified and the shop is consistent.
                        • 9. Re: Multiple authors for a Book
                          Solomon Yakobson
                          sybrand_b wrote:
                          Plural table names are just plain wrong.
                          Tell it to Oracle:
                          SQL> select  count(*)
                            2    from  dba_tables
                            3    where owner = 'SYS'
                            4      and table_name like '%S'
                            5  /
                          
                            COUNT(*)
                          ----------
                                 164
                          
                          SQL> 
                          Few examples:

                          SYS.AUDIT_ACTIONS
                          SYS.CLUSTER_DATABASES
                          SYS.CLUSTER_INSTANCES
                          SYS.CLUSTER_NODES

                          SY.
                          P.S. Plural vs. singular sounds like war in Gulliver's Travels over should all eggs be broken on the smaller end or on the larger end.
                          • 10. Re: Multiple authors for a Book
                            Carlovski
                            Funnily enough I know plenty about formal development procedures. Including the difference between logical entity modelling and physical database design.
                            Where the convention is usually singular for entities, and plural for tables.
                            I don't really care, which is why I had issue with presenting of opinion as fact, but some ORM tools get upset if you don't follow the convention.

                            Carl
                            • 11. Re: Multiple authors for a Book
                              indra budiantho
                              It is like class in java that can be viewed as a blue print or as a set of instances. In the first sense it is singular, but the second term it is plural. Java choose singular for class naming. However, I found the second is semantically more helpful when learning object oriented paradigm. As, in learning language the semantic aspect is more non trivial than syntactical aspect, so for me whether it is plural or singular is a syntactical sugar matter only.

                              For me, to see a Table as a set of records is more helPful than to see it as a template of records.

                              Edited by: Indra Budiantho on Aug 23, 2012 10:29 AM
                              • 12. Re: Multiple authors for a Book
                                Pete_Sg1
                                On list of priorities, I'd rank whether or not a table name is singular or pluarl as WAAAYYY down the list.

                                Syb's advice on table naming convention means a lot to us. As PL/SQL developers are expensive we don't even have a dedicated PL/SQL guy in our team (let alone a data modeller) .
                                C++ guys write our PL/SQL code. I am just beginning to learn Data Modelling and I've found lots of good things in OTN (SQL & PL/SQL forum)

                                Come to think of it, In my old shop, a financial software vendor, where there were lots of PL/SQL gurus, all the table names were Singular.
                                • 13. Re: Multiple authors for a Book
                                  EdStevens
                                  Pete_Sg1 wrote:
                                  On list of priorities, I'd rank whether or not a table name is singular or pluarl as WAAAYYY down the list.

                                  Syb's advice on table naming convention means a lot to us. As PL/SQL developers are expensive we don't even have a dedicated PL/SQL guy in our team (let alone a data modeller) .
                                  C++ guys write our PL/SQL code. I am just beginning to learn Data Modelling and I've found lots of good things in OTN (SQL & PL/SQL forum)

                                  Come to think of it, In my old shop, a financial software vendor, where there were lots of PL/SQL gurus, all the table names were Singular.
                                  And to the point of the value of rigorous - and rigoursly enforced - naming conventions, I'd say Sybrand and I are essentially in agreement.