I decided to do the smart thing and diagram it out - the diagram isn't
perfect (looks wise) but I hope that I can get a +1 before actually
making the databases.
Note: I decided to put rankings with date read table, this way I can
have different rankings for the same book (for each instance that I read
it, some books seem better - or worse - the second time around) :)
Thoughts appreciated, a million thanks for all the advice given so far.
Link:
https://drive.google.com/file/d/0B2kdRhc960qdZzJZenR1Qno2LWM/edit?usp=sharing
Best,
Joel
On 08/10/2014 01:35 PM, Paul wrote:
On Sun, 10 Aug 2014 21:30:18 +0100
Mark Bourne <[email protected]> wrote:
It looks like each book should only have one of the "ReadStatus"
flags set, so I'd make that an enum field on the BookInformation
table, with possible values of "Not Read", "Reading" and "Read". You
can set the default value for the field to "Not Read" so a new record
will be set to that status if no value is specified for that field.
That's essentially the same idea as the status tables I was suggesting.
In my experience we've always used status tables, so I would suggest
those. Partly because I'm not familiar with database support for enums.
How well supported and widely supported is that by the most common
databases?
"NumberOfBooksByAuthor" and "ReadBooksByAuthor" are not needed on the
"AuthorInfo" table - you can get those by querying the database. I
may have the syntax slightly wrong here, but along the lines of:
SELECT `ai`.`AuthorID`, `ai`.`AuthorName`, COUNT(`bi`.`BookID`)
from `AuthorInfo` `ai` LEFT JOIN `BookInformation` `bi` ON
`bi`.`AuthorID` = `ai`.`AuthorID` GROUP BY `ai`.`AuthorID`
should give the number of books by each author. Add:
AND `bi`.`ReadStatus` = "Read"
to the ON condition and you can get the number of read books by each
author.
That's the kind of thing a database enables you to do much more
easily than with a spreadsheet ;o)
Mark.
Joel Madero wrote:
Hi All,
So I went back to planning stage. Link to what I think might work -
hoping to get the planning stage done today so I can start actually
putting together the db - I have 3 days off so now's a good time
for me to get the basic structure together :) Thanks in advance!
https://drive.google.com/file/d/0B2kdRhc960qdbGJIQ1M3NWtrdmc/edit?usp=sharing
Best,
Joel
--
To unsubscribe e-mail to: [email protected]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
