Re: [libreoffice-users] Currently Using Spreadsheet for Personal Project - Thinking About Database

On Sun, 10 Aug 2014 21:30:18 +0100
Mark Bourne <libreoffice-ml.mbourne@spamgourmet.com> 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: users+unsubscribe@global.libreoffice.org
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