Re: table design

Here is a link to the code that I created for the tables.
http://pastebin.com/embed_js.php?i=HSXxX5XP I am going to start running
test data through it today and see how it holds up. The only issue I have
been having was I could not get ON DELETE CASCADE and ON UPDATE CASCADE to
work but I might not be using them properly. I want to use these as they
make propagating changes though all the tuples much easier but they are
not necessary right now as I am the only one testing the design currently.
The referenced tables contain lists and are one way I am trying
to achieve some normalization although I still have some more reading to do
as I don't quite get the concept fully yet. The other reason for this is so
that when I get to the stage of coding a web app front end and creating drop
down menus or other GUI pieces for entering the data I will be able to call
these tables and they will have all the necessary options already available.

I still don't like the idea of using a default entry but that is better then
leaving it blank with a NULL I imagine, at least I should be able to filter
them out this way much easier. My only other thought about this was to have
each '_default_' be specific for the attribute such as '_default_writer_' or
'_default_notes_', that way I could search and replace a set
attributes values much easier.

Thanks for any suggestions or help with this.

On Mon, Feb 14, 2011 at 5:59 AM, Matt <urlugal(dot)mailinglists(at)gmail(dot)com>wrote:

> I did all of the initial design work with a pencil and paper, as I found it
> was the most efficient way. Along the way I figured out that you guys are
> right, related tables are what I need. I drew up all the relationships I
> wanted and laid the db out the best that I could and have started to code
> it. I have a good amount of test data in text files I will copy in and then
> I am going to start running queries against it and see how it holds up and
> what needs to be fixed.
>
> You are right though about a place to start. There is no *simple* tutorial
> or book out there on this subject. There are plenty of books on starting
> with SQL and Postgres but none on how to design a database. The ones I have
> found explain a lot of design ideas but are heavy in Math and then fail to
> implement them or they implement good ideas without explaining why they are
> doing it.
>
> I have a few books and have been slowly making my way through them and the
> combination of them and trial and error to see what works and
> what doesn't has proved to be the best way. The ones I found helpful for me
> anyway are "SQL and Relational Theory" by CJ Date, The Postgres
> Documentation, and "The Theory Of Relational Databases" David Maier.
>
> I am still having a few small issues getting ON DELETE CASCADE and ON
> UPDATE CASCADE to work but I will figure it out soon enough. Thanks for the
> thoughts and suggestions on the books, they were worth the money.
>
> On Sat, Feb 12, 2011 at 6:50 PM, Craig Barnes <cjbarnes18(at)gmail(dot)com>wrote:
>
>> 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
>>
>
>