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

Hi Joel,


On Sun, 10 Aug 2014 16:02:04 -0400
jomali <jomali3945@gmail.com> wrote:

> Joel,
>
> The NumberOfBooksByAuthor and ReadBooksByAuthor fields are
> unnecessary. They can be derived from a simple query for a report.

Agreed.


> Since there has to be a one-to-one relation between the ReadStatus
> table and the BookInformation table, you might as well merge them.

The way you have it rather misses the point of a status table. I
wouldn't merge them, I would instead add a ReadStatusID to the
BookInformation table, and change the ReadStatus table to:

ReadStatusID
ReadStatusDescription


You want to store entries in here like the following:
1  -  Finished Reading
2  -  Not Started
3  -  Busy Reading
4  -  Could Not Finish
5  -  Must Read Soon


that sort of thing.


> Also, you left out a DateRead field, corresponding to the Year,
> Month, Day fields of your original spread sheet.

Yup.


> Similarly, since there has to be a one-to-one relation between the
> BookInformation table and the Rankings table, you might as well merge
> them as well.

Hrm. It does depend on what you want to do with rankings.

You could merge them into the BookInformation table, have a varchar
field that holds a short description of each ranking category for each
book, or you could make this also a status table. Add DepthRankingID,
LessonsRankingID, CitationsRankingID etc fields to the BookInformation
table, and have the Rankings table like so:

RankingID
RankingDescription

With the following sorts of entries:

1  -  Very Poor
2  -  Poor
3  -  Average
4  -  Fair
5  -  Good
6  -  Excellent
7  -  Specially Awesome
8  -  Good but incomplete
9  -  Thorough, but dry
etc


> The NumberOfBooksInSeries, the ReadInSeries and the RemainingInSeries
> fields are unnecessary. Such information is readily obtained through
> queries for reports.

Yup.


> By the way, carefully consider how you intend to import the data from
> your original spreadsheet to the database. There are various ways to
> do it, and how you structure your database will impact how you import
> your data.

Your biggest hassle here will be that the spreadsheet data is
inconsistant. There are considerations here, but trying to keep all of
it in mind might be a bit much right now if this is new to you. I
wouldn't worry too much. It's more important to get the proper database
structure now than to worry about how you will import the data. Once
it's all in place and working, you can worry about getting the data in.
I'm sure plenty of people here can help with that.


Paul




> On Sun, Aug 10, 2014 at 2:48 PM, Joel Madero <jmadero.dev@gmail.com>
> 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
> >
> >
> >  I havne't used Base, but I have taken a very complex spreadsheet
> > and
> > > designed an SQL database from it when it became unwieldy. Key
> > > things I'd suggest, more tables is not bad, in fact the more the
> > > better if it clearly separates data into one table. Do not
> > > duplicate data. If you have a field whose contents are duplicated
> > > then that really probably needs to be a separate table.
> > >
> > > 2 books I found invaluable for helping me design my system are
> > >
> > > Beginning Database Design by Clare Churcher and Beginning SQL
> > > Queries also by Clare Churcher. They were the most readable and
> > > understandable of the lot.
> > >
> > > 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
> > >
> > > Linking should be by query.
> > >
> > > On Aug 8, 2014, at 10:29 AM, Joel Madero <jmadero.dev@gmail.com>
> > > wrote:
> > >
> > >  Just wanted to bump this to see if anyone has thoughts. Worst
> > > case I'm
> > > > going to just dive into Database and see what I can learn but I'm
> > > > hoping to
> > > > get a little feedback before spending time which might be totally
> > > > pointless
> > > > as I really don't have the time to just throw away ;) Thanks
> > > > again in advance!
> > > >
> > > >
> > > >  Eugenie (Oogie) McGuire
> > > Desert Weyr, LLC - Black Welsh Mountain Sheep
> > > http://www.desertweyr.com/ LambTracker - Open Source SW for
> > > Shepherds http://www.lambtracker.com Paonia, CO USA
> >
> > --
> > 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


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