Re: table design

On 11 February 2011 17:08, Brent Dombrowski <brent(dot)dombrowski(at)gmail(dot)com>wrote:

> On Feb 8, 2011, at 12:04 PM, matty jones wrote:
>
> > I may be over-thinking this but I have some questions about table design.
> > The purpose of the database will be to catalog a comic book collection.
> I
> > have been reading the online manual along with a few other books to give
> > myself some ideas of how I want to lay the database out. The problem
> that I
> > am running into is that I have the following tables that inherit from
> other
> > tables.
> >
> >
> > CREATE TABLE BookTitle(
> >
> > booktitle text,
> > compilation text
> > ) INHERITS (SeriesTitle, Storyline, BookCover, ISBN, I_Date, Price,
> Style,
> > StoryTitle);
> >
> > CREATE TABLE StoryTitle(
> >
> > storytitle_id int,
> > storytitle text
> > ) INHERITS (Characters, Writer, Artist, CoverArtist, Letters, Ink,
> Pencils,
> > Colors, Notes);
> >
> >
> > I am using inheritance to get the table structure from other tables. I
> am
> > trying to limit the amount of information in each table, as I *believe*
> this
> > will help keep the table normalized. My question is, is this necessary?
> Is
> > there a way to just have all separate tables and link certain columns
> > together so the same string shows up in each and if I update one column
> it
> > will propagate to other linked columns? That would be my ideal
> situation.
> > I have all the tables coded in a SQL file how I believe they should be
> > setup but it is 120 lines and didn't think it was necessary to post the
> > whole thing. My reason for doing this was because each BookTitle
> contains
> > multiple StoryTitles and each StoryTitle contains an artist, writer,
> etc...
> > Not every BookTitle though will contain Pencils, CoverArtist, etc. My
> line
> > of thinking was to create the structure like this so that when I input
> > the information into a table like so
> >
> > INSERT INTO booktitle (booktitle, i_date, storytitle, name, writer,
> artist,
> > coverartist, seriestitle)
> > VALUES ('Batman #14', 'Dec 42 - Jan 43', 'The Case Batman Failed To
> Solve',
> > 'Batman, Bruce Wayne, Dick Greyson, Robin', 'Don Cameron', 'Jerry
> Robinson',
> > 'Jerry Robinson', 'Batman');
> >
> > It is entered into a single row. but if I were to have the separate
> tables
> > without inheritance, I am not sure how I would be able to relate one
> Artist
> > to a particular StoryTitle. I understand the basics about joins and keys
> > but I don't think keys are what I need here and joins are what I am using
> to
> > output the information I want or is that redundant due to inheritance.
> > Right now I will be the only one using this database but I want to design
> > it correctly so that if others want to use it, they can just copy my code
> > and have a properly designed database. The final goal is to use
> PHP/XHTML
> > to send the query outputs to the browser for the user to view. Most of
> the
> > cataloging programs are stand-alone platform specific or they are
> expensive
> > service-based and charge a fee. I want a program I can access from any
> > modern web browser that is free, and can be custom modified, and I need
> to
> > learn PG for my job so this is a welcome project. I am using Bento on my
> > Mac right now but I can't use it at work(Win7) or on my other
> > computers(Linux) so that won't work and I am sure I am not the only
> person
> > who would like a full featured cataloging program that is run through the
> > browser.
> >
> > Any thoughts or further reading ideas are greatly welcome.
> >
> > Thanks
> >
> > Matty
>
> I don't think inheritance is going to help you here. The insert statement
> you have will not work. You can only insert booktitle and compilation into
> booktitle. You can't insert into the inherited fields. "Inheritance does not
> automatically propagate data from INSERT or COPY commands to other tables in
> the inheritance hierarchy." as per the Postgres Manual.
>
> I think you need to look into dependencies, normal forms, and lossless
> joins. I can't think of any references off the top of my head. Hopefully,
> someone can chime in with some recommendations. Setting up the tables is
> more of a pencil and paper exercise for me. I find it to be something of a
> voodoo art as well.
>
> Brent.
>
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

Hi Matty,

I would agree with Brent in that inheritance isn't going to help you much in
what you are trying to achieve. Based on what I can gleam from your
description related tables are what you need.

I am also not aware of one great place for you to start. I had a scan of
Wikipedia, but alas nothing much for the beginner db designer.

I sometimes use a diagram tool to help visualise the data structure (dia,
vue) especially where complex data structures are needed.

If you like, send in your SQL (or put it in pastebin) and we could start
playing with table design ideas.

Hope this helps.

Craig