Re: [PHP] How to deal with identical fields in db
tedd wrote: > At 3:14 AM -0700 5/6/09, Michael A. Peters wrote: >> Peter Ford wrote: >>> >>> tedd wrote: (and I added in some extra bits...) You need to normalize. Authors should have an unique id in an authors table. The authors table has all the specific information about authors, but not the books they have written. Books should have an unique id in a books table. The books table has all the specific information about books, but not the contributing authors. >>> >>> Like the ISBN, for example - that should be unique enough for anyone... >>> I suppose if you deal in antique books, there might not be an ISBN. >> >> Unfortunately sometimes an otherwise identical but different printing >> of the same book has different ISBN numbers. Sometimes the difference >> is hardback vs softcover, special edition, or just a reprint. >> >> The L.O.C. catalog number may be better, AFAIK there is typically only >> one LOC number per edition of a book. It is a good idea to record both >> (if both exist) and use an internally assigned substitute number when >> one, the other, or both don't exist (small run self published works >> often don't have a LOC number for example, if the author didn't want >> to pay for it). > > > But for a database, a book identifier would probably be best (differing > opinions on this) if it was simply an auto_increment unsigned integer > primary key. A key that is generated upon entry of a book record. > > Certainly one can argue that using a different unique key might provide > more information and make the table require one less field, but if one > uses a primary key, then the field can be searched faster than using a > ISBN or L.O.C., which may be duplicated, amended, or not even present. > My thinking on this is a unique identifier for the book should not be > tied to any attribute of the book, which may change, but rather > something completely detached and artificial. > > Cheers, > > tedd > tedd, That is, in fairness, probably what I'd do too: I might have the ISBN or LOC number as a detail field in the book record, and have it available for look-ups, but the primary key would just be a sequence number generated automatically. Same with authors, just a sequence number for the key. (I am not a number, I am a free man...) These things do not need to be visible to the user. Just an implementation detail, nothing to see here... :) Cheers Pete -- Peter Ford phone: 01580 89 Developer fax: 01580 893399 Justcroft International Ltd., Staplehurst, Kent -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] How to deal with identical fields in db
At 3:14 AM -0700 5/6/09, Michael A. Peters wrote: Peter Ford wrote: tedd wrote: (and I added in some extra bits...) You need to normalize. Authors should have an unique id in an authors table. The authors table has all the specific information about authors, but not the books they have written. Books should have an unique id in a books table. The books table has all the specific information about books, but not the contributing authors. Like the ISBN, for example - that should be unique enough for anyone... I suppose if you deal in antique books, there might not be an ISBN. Unfortunately sometimes an otherwise identical but different printing of the same book has different ISBN numbers. Sometimes the difference is hardback vs softcover, special edition, or just a reprint. The L.O.C. catalog number may be better, AFAIK there is typically only one LOC number per edition of a book. It is a good idea to record both (if both exist) and use an internally assigned substitute number when one, the other, or both don't exist (small run self published works often don't have a LOC number for example, if the author didn't want to pay for it). But for a database, a book identifier would probably be best (differing opinions on this) if it was simply an auto_increment unsigned integer primary key. A key that is generated upon entry of a book record. Certainly one can argue that using a different unique key might provide more information and make the table require one less field, but if one uses a primary key, then the field can be searched faster than using a ISBN or L.O.C., which may be duplicated, amended, or not even present. My thinking on this is a unique identifier for the book should not be tied to any attribute of the book, which may change, but rather something completely detached and artificial. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] How to deal with identical fields in db
Peter Ford wrote: tedd wrote: (and I added in some extra bits...) You need to normalize. Authors should have an unique id in an authors table. The authors table has all the specific information about authors, but not the books they have written. Books should have an unique id in a books table. The books table has all the specific information about books, but not the contributing authors. Like the ISBN, for example - that should be unique enough for anyone... I suppose if you deal in antique books, there might not be an ISBN. Unfortunately sometimes an otherwise identical but different printing of the same book has different ISBN numbers. Sometimes the difference is hardback vs softcover, special edition, or just a reprint. The L.O.C. catalog number may be better, AFAIK there is typically only one LOC number per edition of a book. It is a good idea to record both (if both exist) and use an internally assigned substitute number when one, the other, or both don't exist (small run self published works often don't have a LOC number for example, if the author didn't want to pay for it). -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] How to deal with identical fields in db
tedd wrote: (and I added in some extra bits...) > You need to normalize. > > Authors should have an unique id in an authors table. The authors table > has all the specific information about authors, but not the books they > have written. > > Books should have an unique id in a books table. The books table has all > the specific information about books, but not the contributing authors. > Like the ISBN, for example - that should be unique enough for anyone... I suppose if you deal in antique books, there might not be an ISBN. > Then you connect the two tables with a Book-Author table that has only > the id's of both -- no real need for any other information. > This also has the advantage that when you come to add new books by authors already in the database, you only have to look the name up, and you can avoid duplicating authors with misspelt names, etc. You will have to allow for the case of a book with multiple authors, but that should work out fine - you just have two (or more) records in the Book-Author table to link the same book to several authors, and logic that watches out for that when you extract the data. > That way when you want to see all the books an author has written, then > you pull out all the records that has the author's id and look up each > book via the book id. > > Likewise, when you want to see all the authors who have contributed to a > book, then you pull out all records that has the book's id and look up > each author via their author id. > > Do you see how it works? > > Cheers, > > tedd > It always surprises me how many people need to have database normalisation explained to them - it seems obvious to me... (and tedd, clearly!) -- Peter Ford phone: 01580 89 Developer fax: 01580 893399 Justcroft International Ltd., Staplehurst, Kent -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] How to deal with identical fields in db
Tom Worster wrote: > On 5/5/09 4:42 PM, "Richard S. Crawford" wrote: > > >> On Tue, May 5, 2009 at 1:34 PM, PJ wrote: >> >>> I'm coming up with a bit of a quandry: how to enter and retrieve an >>> identical book title with different authors. >>> It is rather unbelievable what contortions one finds as authors :-( >>> like editors, associations and then the unknowns and anon y mouses. >>> I suppose one has to get really creative... >>> > > don't forget to consider the handling of anthologies :-) > Well, that usually comes under editors and I have a couple of options where to enter that info: sub_title or description fields and I can always add a for author first_name and Various or whatever for last_name :-) > > >>> Anyone for tea? >>> > > yes please, i'd love some. > > > >> What I've done for this sort of project in the past was create >> separate tables for authors, books, and author relationships (e.g., >> author, translator, editor), then linking tables for each of those. >> You seriously want to do some normalization on this task; otherwise, >> you end up with a giant table of books, with multiple rows duplicating >> the title of the book, leading to a huge "books" table, and nobody >> wants that. >> > > i have a db with 10s of millions of artists, disks, songs etc. i've tried it > both ways. and after 5 years working with it i still can't make up my mind > which way i prefer it. i keep finding pros and cons to each approach that > differ depending on what functionality i'm programming. i will never have a > simple answer. > > so i can't help answer the original question other than to say that, for me, > personally, in my opinion, i don't accept the dogma that normal forms are > always good for you. they might be. it depends. it's like being dogmatic > about specific foods without taking the overall diet and lifestyle into > account. despite the simple dogma some may espouse, whether or not a big mac > with fries is bad for you depends on many factors. > > in any case, it's amazing what you can do these days with one huge table and > some well chosen indexes. and it's amazing how mind bending it can get when > joining 5 data tables using 3 join tables. > ain't that the truth ! > good luck, phil. > Thank you guys, for the input. Never thought so many would help so few(little me). ;-) I'm really just a little shorter than BG at 6'5" :-D Actually, I started out and still am with the db normalized. It all works quite well, it's just frustrating to have to go through all the contortions to check things. I started out with just checking the title, then had to add a check to the sub_title, (already have a check for author) but now have to add another to go with the specific book... oh, well... all a part of the learning process. :-) -- Hervé Kempf: "Pour sauver la planète, sortez du capitalisme." - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] How to deal with identical fields in db
On 5/5/09 4:42 PM, "Richard S. Crawford" wrote: > On Tue, May 5, 2009 at 1:34 PM, PJ wrote: >> I'm coming up with a bit of a quandry: how to enter and retrieve an >> identical book title with different authors. >> It is rather unbelievable what contortions one finds as authors :-( >> like editors, associations and then the unknowns and anon y mouses. >> I suppose one has to get really creative... don't forget to consider the handling of anthologies :-) >> Anyone for tea? yes please, i'd love some. > What I've done for this sort of project in the past was create > separate tables for authors, books, and author relationships (e.g., > author, translator, editor), then linking tables for each of those. > You seriously want to do some normalization on this task; otherwise, > you end up with a giant table of books, with multiple rows duplicating > the title of the book, leading to a huge "books" table, and nobody > wants that. i have a db with 10s of millions of artists, disks, songs etc. i've tried it both ways. and after 5 years working with it i still can't make up my mind which way i prefer it. i keep finding pros and cons to each approach that differ depending on what functionality i'm programming. i will never have a simple answer. so i can't help answer the original question other than to say that, for me, personally, in my opinion, i don't accept the dogma that normal forms are always good for you. they might be. it depends. it's like being dogmatic about specific foods without taking the overall diet and lifestyle into account. despite the simple dogma some may espouse, whether or not a big mac with fries is bad for you depends on many factors. in any case, it's amazing what you can do these days with one huge table and some well chosen indexes. and it's amazing how mind bending it can get when joining 5 data tables using 3 join tables. good luck, phil. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] How to deal with identical fields in db
At 4:34 PM -0400 5/5/09, PJ wrote: I'm coming up with a bit of a quandry: how to enter and retrieve an identical book title with different authors. It is rather unbelievable what contortions one finds as authors :-( like editors, associations and then the unknowns and anon y mouses. I suppose one has to get really creative... Anyone for tea? You need to normalize. Authors should have an unique id in an authors table. The authors table has all the specific information about authors, but not the books they have written. Books should have an unique id in a books table. The books table has all the specific information about books, but not the contributing authors. Then you connect the two tables with a Book-Author table that has only the id's of both -- no real need for any other information. That way when you want to see all the books an author has written, then you pull out all the records that has the author's id and look up each book via the book id. Likewise, when you want to see all the authors who have contributed to a book, then you pull out all records that has the book's id and look up each author via their author id. Do you see how it works? Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] How to deal with identical fields in db
PJ wrote: I'm coming up with a bit of a quandry: how to enter and retrieve an identical book title with different authors. It is rather unbelievable what contortions one finds as authors :-( like editors, associations and then the unknowns and anon y mouses. I suppose one has to get really creative... Anyone for tea? You should have a title_id field in your titles table, and this field should be unique. Probably auto assigned and auto increment. If you search for a book title, and there are two books with that title, you should get two records back! Stephen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] How to deal with identical fields in db
On Tue, May 5, 2009 at 1:34 PM, PJ wrote: > I'm coming up with a bit of a quandry: how to enter and retrieve an > identical book title with different authors. > It is rather unbelievable what contortions one finds as authors :-( > like editors, associations and then the unknowns and anon y mouses. > I suppose one has to get really creative... > Anyone for tea? What I've done for this sort of project in the past was create separate tables for authors, books, and author relationships (e.g., author, translator, editor), then linking tables for each of those. You seriously want to do some normalization on this task; otherwise, you end up with a giant table of books, with multiple rows duplicating the title of the book, leading to a huge "books" table, and nobody wants that. -- Richard S. Crawford (rscrawf...@mossroot.com) http://www.mossroot.com Publisher and Editor in Chief, Daikaijuzine (http://www.daikaijuzine.com) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] How to deal with identical fields in db
I'm coming up with a bit of a quandry: how to enter and retrieve an identical book title with different authors. It is rather unbelievable what contortions one finds as authors :-( like editors, associations and then the unknowns and anon y mouses. I suppose one has to get really creative... Anyone for tea? -- Hervé Kempf: "Pour sauver la planète, sortez du capitalisme." - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
