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

Hi Joel,


On Fri, 8 Aug 2014 10:53:12 -0700
Joel Madero <jmadero.dev@gmail.com> wrote:

> So my thought was to have three tables -
> *Table - BooksRead*
> Author
> Book
> SeriesName (can allow Null because most books are not in a series)
> Pages
>
> *Table - Series*
> SeriesName (not unique per row)
> BookName (This + SeriesName would be unique)
> Read (binary true/false auto set dependeing on if the book is in
> BooksRead or not)

This just duplicates information in the BooksRead table, not a good idea


> *Table - Rating*
> Each category (ie, story, ending, character development, etc...)
> Overall - auto calculated based on combo of other values
> (here I'd love to have some kind of graphical representation of my
> ratings, like stars, incremented by half star, 1-5 star total)

Calculating the overall value may be tricky, depending on how the other
fields are done, and almost impossible if you want to give descriptive
ratings like you have in places in the spreadsheet. Once you've gotten
it down to a number, though, showing stars instead of the number is
doable, I'm just not sure how easy in Base.

Also, with the above setup, you don't have anywhere to record books you
want to read (except in the series table, and so only books in a
series), like you have in the spreadsheet.


> *So then what would be left is:*
> Figuring out how to summarize my data like I do in the spreadsheet
> (pages read per year, pages remaining, pages read per month, etc....)

That would be some form of report, and once the data is in proper
tables, fairly easy to do.


> Figuring out how to get some printout of "next book in series" - I
> suspect some kind of query would work that checks in "Series" table
> and finds the "next" book in a given series that does not have a true
> value in "Read" field. In spreadsheet I was going to use match/index
> to accomplish this.

Something like select all the books in the series table that don't have
an entry in the read books table (going by name, author and series),
ordering that list by the number of the book in the series, and showing
only the first entry of the resulting list, should do it.

Maybe not a simple query, but pretty easy to whip up if you know a
little SQL.

> Other cool things like summarizing my opinions of particular authors,
> a list of "want to read" books independent of the series. Possibly
> add genre somewhere and a randomized "suggest a book" thing.

All easier to do in a database than a spreadhseet.


> At some point I'd love to figure out how to pull series data from some
> source so I don't have to manually add books that are in a series.
> Some series have 50+ books, entering them all manually might be
> brutal ;)

Hrm, that may require a little actual programming...


-- 
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