Parsing Table columns?

[Jim Crowell]

Hi,
I have just installed Derby into my Java stand alone application. I have a table design question.

Lets say I have a table that consist of a primary key and 26 columns [A, B, ,,,Z].

With SQL statements I can select individual columns into a result-set and judicially insert new rows or replace the values in selected row / cols.

Question:
Are there any guideline articles or books that will help me in designing my tables?

For example, for the above table, I may do the majority of the run-time processing on columns A, B and C. Should I place the primary key and columns A, B and C in an individual table and maintain a second table with the primary key and columns D ... Z. Or am I over thinking this too much.

Note that my Java stand alone application is form centric and I think the Derby  access time is not all that critical since there will be a lot of data entry processing that will mask any SQL time inefficiencies.

As you can perhaps tell, I am new to Database implementation and want to do this initial design properly.

[Bryan Pendleton]

> Are there any guideline articles or books that will help me in designing my tables?

Here's a good place to start:
http://www.google.com/search?q=data+normalization

> I am new to Database implementation and want to do this initial design properly.

Well, you've made an excellent first step by realizing that this is
a topic worth thinking about. Too many people just dive right in,
so you're off to a great start by resisting that urge and thinking
about it first!

After you've thought about it some more, and read some of the
articles that are available, come back to this list with more
detailed questions and you'll certainly find people happy to
discuss this at length.

thanks,

bryan

[Rick Hillegas-2]

Hi Jim,

I agree with Bryan that you are off to a great start since you realize
that this is a topic which requires some design and thought. Last year I
ran a BOF at SIGSCE on the topic of teaching college level database
courses. It turned out that at American colleges today, the favorite
textbook on this topic is Ramakrishnan and Gehrke's Database Management
Systems:

 
http://www.agapea.com/libros/Database-Management-Systems-isbn-0071230572-i.htm

A little googling turned up the following slidedeck accompanying this
textbook:

 
http://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/slides/slides3ed.html

Hope this helps,
-Rick

Bryan Pendleton wrote:

>> Are there any guideline articles or books that will help me in
>> designing my tables?
>
> Here's a good place to start:
> http://www.google.com/search?q=data+normalization
>
>> I am new to Database implementation and want to do this initial
>> design properly.
>
> Well, you've made an excellent first step by realizing that this is
> a topic worth thinking about. Too many people just dive right in,
> so you're off to a great start by resisting that urge and thinking
> about it first!
>
> After you've thought about it some more, and read some of the
> articles that are available, come back to this list with more
> detailed questions and you'll certainly find people happy to
> discuss this at length.
>
> thanks,
>
> bryan
>
>

[Derby-3]

What?
When did they stop using C J Date? Or am I now showing my age? :-)

> -----Original Message-----
> From: Richard.Hillegas@... [mailto:Richard.Hillegas@...]
> Sent: Thursday, June 11, 2009 1:12 PM
> To: Derby Discussion
> Subject: Re: Parsing Table columns?
>
> Hi Jim,
>
> I agree with Bryan that you are off to a great start since you realize
> that this is a topic which requires some design and thought. Last year I
> ran a BOF at SIGSCE on the topic of teaching college level database
> courses. It turned out that at American colleges today, the favorite
> textbook on this topic is Ramakrishnan and Gehrke's Database Management
> Systems:
>
>
> http://www.agapea.com/libros/Database-Management-Systems-isbn-0071230572-
> i.htm
>
> A little googling turned up the following slidedeck accompanying this
> textbook:
>
>
> http://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/slides/slides3ed.
> html
>
> Hope this helps,
> -Rick
>
> Bryan Pendleton wrote:
> >> Are there any guideline articles or books that will help me in
> >> designing my tables?
> >
> > Here's a good place to start:
> > http://www.google.com/search?q=data+normalization
> >
> >> I am new to Database implementation and want to do this initial
> >> design properly.
> >
> > Well, you've made an excellent first step by realizing that this is
> > a topic worth thinking about. Too many people just dive right in,
> > so you're off to a great start by resisting that urge and thinking
> > about it first!
> >
> > After you've thought about it some more, and read some of the
> > articles that are available, come back to this list with more
> > detailed questions and you'll certainly find people happy to
> > discuss this at length.
> >
> > thanks,
> >
> > bryan
> >
> >

[Jim Crowell]

Bryan Pendleton wrote:

> Are there any guideline articles or books that will help me in designing my tables?

Here's a good place to start:
http://www.google.com/search?q=data+normalization

Bryan,
I found the following link via the link you provided:
http://www.agiledata.org/essays/dataNormalization.html

The above link gave me a nice understanding of the “#NF” Database normalization states.

I am sure that, as a minimum, I have a 1NF Database normalization design in that there is no redundant data.

My Derby usage is a little different than most. I currently have a HDD driven system where there is an Address Book HDD file and a HDD file for each form of my “Form Centric”  Java stand alone application. By design the form classes have system unique names and each field of a form has a unique name. The concatenations of the form ID and the field ID yield a unique ID for any field of the Application. Kinda like a primary Database key.

The stand alone application produces a series for reports derived as a function of the form data fields. I need to produce the same reports from the stand alone application or from a web page. Therefore, the web implementation requires the Address Book and the form field values to exist via my server.

I am installing the Derby  Database to implement form field and Address Book parameters persistence and to make this data available in both the stand alone and web environments to my Report Generator.

Currently I envision 5 Database Table types:
        1) Address Book
  2) System Parameters
        3) Form Fields
        4) Form Info
        5) Form State

There shall be a Derby  Database Table for each data entry form [3) above]. Each form table shall consist of an ordered pair [key/value] where the key is the unique field ID and the value is the current value assigned to the associated field in Java String format.

Before I read your referenced link I had planned to let the “Form Fields” table contain ordered pairs for the forms two inner classes [info | state] that would have been stored in the form table value column in the inner class ‘toString’ format.

Now I think a table for each with a row for each form is more efficient. The form ID shall be the key and inner class specific columns will be designed.

I do not know how to evaluate this design for the 2NF or 3NF normalization formats but I think the above table layout does the job for me.

Various columns of the Form State table [5) above] will be updated each time the application end user moves to a new form field. It is this table that I was primarily concerned about when I started this thread. I think I’ll implement each row as a series of state field columns and see if any additional tweaking is required later.

Another unmentioned requirement for my Derby  Database implementation is the need to be able to recreate the HDD files if they are unavailable for some reason. I think I am OK there and the Database table design is not important for this recovery processing.

Thanks for your help,
Jim...