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

Really good suggestions!


On Sun, 10 Aug 2014 10:50:48 -0600
Oogie McGuire <oogiem@desertweyr.com> wrote:

> Do not duplicate data. If you have a field whose contents are
> duplicated then that really probably needs to be a separate table. 

Yes, true. Note that if a field is duplicated, is suggests
consolidating into one table, but if the contents of a field are
duplicated, it suggests splitting it off into its own table.


> If I were doing your system I'd do the following:
> Book table
>       Title
>       number of pages
>       Foreign key links to an authors table and a series table
>       boolean read or not or else a link to a table read status see
> below 
> Authors
>       Name
> Series
>       Name of series like Dragons of Pern or Harry Potter
> Read status
>       started
>       finished
>       wanted

Having a separate table for Authors and Series is a good idea. I didn't
want to confuse the OP with a whole bunch of tables straight off the
bat, but you're dead right, it is probably best to get to grips with
this now, rather than need it later and not have it designed in.

And a status table allowing more than just read or unread is a good
idea too. It adds flexibility to the system.

This does make the system a little more complicated. You will have
more forms, more dropdowns on the book entry form, and will have to
remember to do things like add the authors before you add their books,
but this added complexity will pay off in terms of extra flexibility
later.

This is also really the big conceptual difference between a spreadsheet
and a database. In a spreadsheet you start just capturing a few books,
and just retype things like author names, and then you end up with lots
of books, and things start getting harder and harder to keep track of.
In a database you start off by separating everything out into different
bits, then combine them in the different ways you need them, and
everything is slightly more complicated (especially to begin with), but
actually easier to work with in the long run.

To Joel (the OP):
You'll need to read up on one-to-many and many-to-many relationships if
you aren't already familiar with them.

Some of the less used columns in you spreadsheet, like "Loc", I'm not
clear on what they're used for and where they should go, and rating
has been used in different ways, so again I'm not sure how you intend
to use that. You do kind of need to decide on one way and stick to it
throughout. An advantage of spreadsheets is that you can just do stuff
in that sort of ad-hoc manner as it seems appropriate to the individual
instance, but in databases, because of the extra structure, you need to
stick to one way of doing things. But that does make it more
consistant, and therefore easier to work with in the long run.


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