Parsing Table columns?

[Jim Crowell <Ji...@EMail.com>]

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.


-----
Regards,
Jim...
-- 
View this message in context: http://www.nabble.com/Parsing-Table-columns--tp23982327p23982327.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Parsing Table columns?

[Jim Crowell <Ji...@EMail.com>]

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

-----
Regards,
Jim...
-- 
View this message in context: http://www.nabble.com/Parsing-Table-columns--tp23982327p24001839.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

RE: Parsing Table columns?

[de...@segel.com]

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

> -----Original Message-----
> From: Richard.Hillegas@Sun.COM [mailto:Richard.Hillegas@Sun.COM]
> 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
> >
> >

Re: Parsing Table columns?

[Rick Hillegas <Ri...@Sun.COM>]

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

Re: Parsing Table columns?

[Bryan Pendleton <bp...@amberpoint.com>]

> 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