Re: [PHP] How to deal with identical fields in db

2009-05-06 Thread Peter Ford


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

2009-05-06 Thread Michael A. Peters

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

2009-05-06 Thread tedd

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

2009-05-06 Thread Peter Ford
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

2009-05-05 Thread Richard S. Crawford
On Tue, May 5, 2009 at 1:34 PM, PJ af.gour...@videotron.ca 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



Re: [PHP] How to deal with identical fields in db

2009-05-05 Thread Stephen

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

2009-05-05 Thread tedd

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

2009-05-05 Thread Tom Worster
On 5/5/09 4:42 PM, Richard S. Crawford rscrawf...@mossroot.com wrote:

 On Tue, May 5, 2009 at 1:34 PM, PJ af.gour...@videotron.ca 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

2009-05-05 Thread PJ
Tom Worster wrote:
 On 5/5/09 4:42 PM, Richard S. Crawford rscrawf...@mossroot.com wrote:

   
 On Tue, May 5, 2009 at 1:34 PM, PJ af.gour...@videotron.ca 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 nbsp; 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