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

Hi Joel,



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

> Neater is exactly what I'm aiming for. Currently it's kind of all
> over the place and with me continuously adding things I am always
> debating where to put things, how to organize, how to easily view it,
> etc.. etc... So neater is a big + for me.
>
> Unwieldy is where I'm at with this thing, and it's only getting
> worse ;)
>
> It does indeed work and I'm pretty good with spreadsheets generally
> but - well at my old job we had someone who had a 1.5 gig spreadsheet
> (and it was absurd) and his logic was the same "it works" - I had a
> hard time not saying "it works but it doesn't work RIGHT" - I'm
> starting to feel like my spreadsheet doesn't work right (from
> functionality to organization to me just being happy with it)

Well, sounds like changing to a database is the way to go, then :)


> I will most definitely take you up on this. I guess my first step
> would be a table that just has:
> UniqueID
> Author
> Book Title
> Series Name
>
> That sound right? I'm going to start after work today.

Well, you have a few choices:

a) you could have one table for books read and one for the books still
to read, and when you've read a book you have the database transfer the
record from the one table to the other.

b) you could have a table for all books, and a flag in the table to
show when it's read

c) you could have a table for all books, and another table with your
rating and such details that gets a record added when you've read the
book

Unfortunately, such choices are the ones we need to make when designing
the database, and it can sometimes be hard to know which is the best
choice.

Your current spreadsheet looks a little like scenario (a) with the two
worksheets, but it's not the best idea for the database, I feel.
Scenario (c) avoids having to have nulls in the table, but the data will
only ever be used joined to the first table, so it essentially comes
down to the same thing. I lean towards scenario (b) myself.

As such, the table would be called Books, and have the following fields:

BookID (or UniqueID, or just ID)
Author
Title
SeriesName
-------------
DateRead
Rating
Pages
StoryOrCitations (perhaps this field should be split?)
CharacterOrDepth (perhaps this field should be split?)
Lessons
WritingStyle
Ending
Overall
Size
LocNum
Type

The fields above the line should not allow nulls, the ones below the
line should. Thank a little about which fields you actually use, I
think some of them look like they are only partly used, maybe you
changed your mind on some of them.

Mostly the fields should be either an integer, or a varchar, with one
date.

Adding books should be easy, if a book has not been read, everything
below the line will be a null, if the book has been read, everything
below the line should be not null, even if it is simply blank.

So the flag to tell if the book has been read would be if it has a date
that is not null.

That should be something to get started with.


> P.S. I won't lose functionality that I have in spreadsheet when
> using database right? Basically everything you can do in a
> spreadsheet you can accomplish in a database?

Well, you shouldn't lose any functionality, databases can do everything
you can in a spreadsheet, and more, it's just a question of how hard it
is. Mostly what you seem to be using the spreadsheet for is pretty
straightfoward stuff, so you should be able to get all that done in a
database without too much hassle.



Paul

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