Do these proposed tables for a books database to be used on www.achuka.co.uk appear manageable? If not, I would very much welcome the advice of the collective MySQL mind...

AUTHORS
AuthID
AuthFirst
AuthLast
AuthProfile
AuthSite

ILLUSTRATORS
IllID
IllFirst
IllLast
IllProfile
IllSite

BOOKS
BookID
ISDN
Title
AuthID
IllID
PubID
Format
Month
Year
Category
Key1
Key2
Key3
Rating
Comment
ReviewID

PUBLISHERS
PubID
PubName
Address
Tel
Fax
Email
Publicist
Editorial

REVIEWS
RevID
ISDN
(Title)
(Author) [where ISDN unknown]
NewsMagID
Date
Quote

NEWSMAGS
NewsMagID
Name

Michael

    Seems good and simple, but, the reviewID field in the book table is a match for revid in the reviews table?, that would allow to have only one review for book, that's ok?

      but, the reviewID field in the book table is a match for revid in the reviews table?, that would allow to have only one review for book, that's ok?

      No, not OK. Thanks for poitning that out. I need to think about referencing 'books' to multiple reviews...
      Thanks

        Also remember books can have more than one author.

          Will you be setting up full-text search on the tables? YOu would probably want to do so if you have alot of books/authors in the database...

            Will you be setting up full-text search on the tables?

            Yes, I imagine so, in addition to tight queries. You suggest there are implications, but don't say what they are...

              here are general problems with your design:

              1. you're using mixed case. It's best to use all lower case when working with database table and column names
              2. you prepend a part of the table name to each column in a table. This is completly unnecessary and only makes reading the tables more difficult.

              Other problems, but these may just be my infamiliarity with library catalogs:

              1. why is there a month and year in the books table? Why not replace it with a datetime field?
              2. the relationship between books, reviews and magazines seems way too simple. There should probably be a couple of many-many join tables between them.
              3. books.key[1-3] are extremly poorly named columns. Each column in a table should be named such that it's easy to recogize what the data in that column is used for.
              4. books.category should be a foreign key to a category table.
              5. only one illustrator is allowed per book...that's bad form. fix it.
              6. rating should be an average of user ratings for the book...not a fixed rating. Create a new rating table and have a foreign key to books in that table.

              I could go on and on.....check out sql for smarties, or if that's too advanced (not being rude, but it is an advanced book), start with some other sql design book. Don't read books that are specific to a database or language...read only general design books.

                Thanks Toma
                these is really helpful criticism... much appreciated:

                1. you're using mixed case. It's best to use all lower case when working with database table and column names

                easily fixed, and I'll certainly do that

                1. you prepend a part of the table name to each column in a table. This is completly unnecessary...

                good, but I thought it would be needed to avoid possible confusion between Author First Name and Illustrator First Name etc.

                Other problems
                1. why is there a month and year in the books table? Why not replace it with a datetime field?

                Because I want a book's publication date to appear as Jul 2002 or Oct 2002 and I didn't thinkt there was a datetime field that would result in this format display

                1. the relationship between books, reviews and magazines seems way too simple. There should probably be a couple of many-many join tables between them.

                Mmm, I'll have to think and read around this one

                1. books.key[1-3] are extremly poorly named columns. Each column in a table should be named such that it's easy to recogize what the data in that column is used for.

                Right, I was aware of this, and it was a bit of stopgap. The difficulty I have is that originally I was thinking of having separate tables for Fiction, Poetry, Picture Books etc. then thought it would be more sensible to have all books in one table, but to have a Category entry which could be used to sort them into Fiction, Poetry, etc. Trouble is, I need to sub-categorise, the Non-Fiction books into their subject matter, and Picture Books into different types, and was thinking of using the wild key columns to do that...

                1. books.category should be a foreign key to a category table.

                This ives me a good lead, thanks

                1. only one illustrator is allowed per book...that's bad form. fix it.
                  Same with author. Yes, someone else pointedd that out.

                2. rating should be an average of user ratings for the book...not a fixed rating. Create a new rating table and have a foreign key to books in that table.
                  No. The rating will be the website not a user rating. So it WILL be fixed.

                I could go on and on.....check out sql for smarties, or if that's too advanced (not being rude, but it is an advanced book)

                Not being rude at all - you've correctly deduced that I'm feeling my way with this!

                  1. you prepend a part of the table name to each column in a table. This is completly unnecessary...
                    good, but I thought it would be needed to avoid possible confusion between Author First Name and Illustrator First Name etc.

                  No: author.name, illustrator.name there you go: no confusion at all. you can speicify the table name where you need and you'll thank me in the long run.

                  Other problems
                  1. why is there a month and year in the books table? Why not replace it with a datetime field?
                  Because I want a book's publication date to appear as Jul 2002 or Oct 2002 and I didn't thinkt there was a datetime field that would result in this format display

                  No: date('m/Y', $timestamp); will give you the format you want. Read about the date() function for more info.

                  1. books.key[1-3] are extremly poorly named columns. Each column in a table should be named such that it's easy to recogize what the data in that column is used for.
                    ...
                    Poetry, etc. Trouble is, I need to sub-categorise, the Non-Fiction books into their subject matter, and Picture Books into different types, and was thinking of using the wild key columns to do that...

                  Use a category hierarchy. There is a fantastic method outlined here for trees (from sql for smarties):
                  http://www.dbmsmag.com/9605d06.html

                  1. books.category should be a foreign key to a category table.
                    This ives me a good lead, thanks

                  see #3

                    Does anyone know of a slightly clearer, user-friendly online guide to category hierarchies and methods for establishing trees than the one posted above, which is a bit out of my league, for the time being!

                      Write a Reply...