table design

From: matty jones <urlugal(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: table design
Date: 2011-02-08 20:04:37
Message-ID: AANLkTinsm_1wqk0_zZxgbTE3T4KfbLWic6b3Ro6pj4pX@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Jasen Betts 2011-02-09 10:57:30 Re: ERROR: invalid datatype 'FILE'
Previous Message Lew 2011-02-06 17:53:20 Re: Backing up several PostgreSQL databases