Board index » delphi » Need advice - first major Database App

Need advice - first major Database App

I have been working with Delphi for a couple of months, and I love it.
Now a project has come up that goes quite a bit beyond what I have
done so far, and I could use a bit of advice.  

An association that administers funding to non-profit groups wants to
computerize their grant application forms.  I will be creating a
Delphi app with on-screen versions of the forms to be filled out.  My
question relates to how I should store the data.  

I did a similar project a couple of years ago using Turbo Pascal (for
DOS).  That time, I simply stored the info as one big binary file.  I
had variables for all the "fields", and I just wrote/read them to a
file.  A primitive method, but it worked fine for what I needed.  Then
I wrote a utility to export the data to a standard format.  But I'm
thinking that I should use the database features of Delphi, and maybe
store the data as a Paradox database.  One advantage of this is that
the association already uses Paradox, so they would be able to create
queries and reports directly on the data, without my having to
"export" it to Paradox format first.  

But I'm not sure how to set up the tables.  There is not a lot of
repeating data, so it would not be a "typical" database.  Think of a
bank loan application, only 20 pages long.  Maybe each funding
application should be one record in a master table, and each field
should be one "question" on the application.  Some of the questions
are long answer (a memo field), some are true/false (boolean), and
some require a table of values to be filled out (a detail table?).  

The likely file format is Paradox 5.0 for Windows.  How many fields
can you have in a Paradox 5.0 table?  I may need several hundred if I
use one field for each question.  

Or maybe there is a better way to store the data.  Any suggestions or
comments would be greatly appreciated.

Warren Sande
EcoTech Research
Regina, SK
ecot...@unibase.unibase.com
Voice: (306) 352-2468

 

Re:Need advice - first major Database App


Quote
Warren Sande (ecot...@unibase.unibase.com) wrote:

<snip>
: Or maybe there is a better way to store the data.  Any suggestions or
: comments would be greatly appreciated.

: Warren Sande
: EcoTech Research
: Regina, SK
: ecot...@unibase.unibase.com
: Voice: (306) 352-2468

I would definitely break it down into several DB's. One for the
applicants general info (name/adress etc) and then perhaps try to categorize
the other info in some way, say one DB for financial info, one for
company info (type of business etc). Perhaps one DB for each of the 20
(or so) pages you mentioned. This also makes it faster for any queries
they would like to make for statistical purposes as well as preserving
resources when displaying an application and perhaps making it easier for
the user to select the information he/she is interested in and displaying
it in a separate window.

Just my 2 cents...:)

   /Johan...!

Re:Need advice - first major Database App


Warren:

If you need to recombine keyed tables from different sources, a easy way
to handle this would be to have the source be part of the key (unique for
each location).  This is easy with Paradox tables since you can define
the primary key to one or more fields.  Not sure if you can combine a
auto-increment field with another field for the promary key; try it.

HTH
Glen Mann
gm...@haven.ios.com

Quote
Warren Sande (ecot...@unibase.unibase.com) wrote:

: Obviously, I need a way to link the varoius tables.  So each table
: will have an "ID_NUMBER" field that I can use to join the tables.  I
: took a first run at this, and I made ID_NUMBER an Auto-Increment field
: in the main table, and a LongInteger field in all the other tables.
: So, when a new applicaton is started (a new record added), the main
: table sets the ID_NUMBER, then the I will have the other tables read
: that value and use the same ID_NUMBER.  No problem so far.

: But each applicant will have their own copy of the program running on
: their own system, with their own set of tables.  When they send in a
: disk with all the data, the funding administrators have to "import"
: the record into their copy of the program, with their own tables.
: Several applicants could have the same value for ID_NUMBER, since they
: were all created on different systems.  But the ID_NUMBERs must be
: unique when then the administrator puts all the applications on his
: system.  How should I handle this?  (I have some ideas, but I would
: like to hear your opinions.)

: Thanks for the assistance.
:    

: Warren Sande
: EcoTech Research
: Regina, Saskatchewan, Canada
: ecot...@unibase.unibase.com

Re:Need advice - first major Database App


Quote
w...@sophocles.algonet.se (Johan Alveborg) wrote:
>I would definitely break it down into several DB's. One for the
>applicants general info (name/adress etc) and then perhaps try to categorize
>the other info in some way, say one DB for financial info, one for
>company info (type of business etc). Perhaps one DB for each of the 20
>(or so) pages you mentioned. This also makes it faster for any queries
>they would like to make for statistical purposes as well as preserving
>resources when displaying an application and perhaps making it easier for
>the user to select the information he/she is interested in and displaying
>it in a separate window.

Thanks for the advice.  I think I am going to do it this way.  But I
have another question relating to this.  

Obviously, I need a way to link the varoius tables.  So each table
will have an "ID_NUMBER" field that I can use to join the tables.  I
took a first run at this, and I made ID_NUMBER an Auto-Increment field
in the main table, and a LongInteger field in all the other tables.
So, when a new applicaton is started (a new record added), the main
table sets the ID_NUMBER, then the I will have the other tables read
that value and use the same ID_NUMBER.  No problem so far.

But each applicant will have their own copy of the program running on
their own system, with their own set of tables.  When they send in a
disk with all the data, the funding administrators have to "import"
the record into their copy of the program, with their own tables.
Several applicants could have the same value for ID_NUMBER, since they
were all created on different systems.  But the ID_NUMBERs must be
unique when then the administrator puts all the applications on his
system.  How should I handle this?  (I have some ideas, but I would
like to hear your opinions.)

Thanks for the assistance.

Warren Sande
EcoTech Research
Regina, Saskatchewan, Canada
ecot...@unibase.unibase.com

Re:Need advice - first major Database App


Quote
In article <4asa4v$...@udevdiv.Unibase.COM> ecot...@unibase.unibase.com (Warren Sande) writes:
>>I would definitely break it down into several DB's. One for the
>>applicants general info (name/adress etc) and then perhaps try to categorize
>>the other info in some way, say one DB for financial info, one for
>>company info (type of business etc). Perhaps one DB for each of the 20
>>(or so) pages you mentioned. This also makes it faster for any queries
>>they would like to make for statistical purposes as well as preserving
>>resources when displaying an application and perhaps making it easier for
>>the user to select the information he/she is interested in and displaying
>>it in a separate window.
>Thanks for the advice.  I think I am going to do it this way.  But I
>have another question relating to this.  
>Obviously, I need a way to link the varoius tables.  So each table
>will have an "ID_NUMBER" field that I can use to join the tables.  I
>took a first run at this, and I made ID_NUMBER an Auto-Increment field
>in the main table, and a LongInteger field in all the other tables.
>So, when a new applicaton is started (a new record added), the main
>table sets the ID_NUMBER, then the I will have the other tables read
>that value and use the same ID_NUMBER.  No problem so far.
>But each applicant will have their own copy of the program running on
>their own system, with their own set of tables.  When they send in a
>disk with all the data, the funding administrators have to "import"
>the record into their copy of the program, with their own tables.
>Several applicants could have the same value for ID_NUMBER, since they
>were all created on different systems.  But the ID_NUMBERs must be
>unique when then the administrator puts all the applications on his
>system.  How should I handle this?  (I have some ideas, but I would
>like to hear your opinions.)

First of all, "beware the Ides of March."  If you ever have to repair one
of those tables, your autoincrements are going to be reset to start at 1 and
you'll lose those precious IDs.

Second, you need to have *some* unique identifier, like an SSN, that can be
recognized no matter who entered the record.  So the choice of using an
autoincremented integer to act as the key is probably unwise to begin with in
this case.

If you can't do this, then the import routine is going to have to grab the
tables in, verify the links between them, develop some key (e.g. a name) that
IS unique, and deal with whether-or-not it's in the database now.  It will
have to have logic to re-map the IDs used in the imported table to those in
the database... for both "replace existing records" and "inserting record that
was not in the master before" cases.

I think you can see why an SSN would be so much simpler.  :-/

/mr/
{SSN=social security number}

Re:Need advice - first major Database App


Quote
sund...@primenet.com (Sundial Services) wrote:
>In article <4asa4v$...@udevdiv.Unibase.COM> ecot...@unibase.unibase.com (Warren Sande) writes:

>>Thanks for the advice.  I think I am going to do it this way.  But I
>>have another question relating to this.  

>>Obviously, I need a way to link the varoius tables.  So each table
>>will have an "ID_NUMBER" field that I can use to join the tables....

>>But each applicant will have their own copy of the program running on
>>their own system, with their own set of tables.  When they send in a
>>disk with all the data, the funding administrators have to "import"
>>the record into their copy of the program, with their own tables.
>>Several applicants could have the same value for ID_NUMBER, since they
>>were all created on different systems.  But the ID_NUMBERs must be
>>unique when then the administrator puts all the applications on his
>>system.  How should I handle this?

I faced a similar problem years ago (dBase III+) and did it this way:

1) A nice long ID field (I think I used 10 bytes), _not_ autoincrementing

2) Each machine that can add records has its own range of ID numbers
assigned (i.e. machine 1 has numbers from 10000000 to 19999999, 2 from
20000000 and so on).

3) These ranges are stored in each machine's .INI file, where you can
also keep track of the ID assigned to the last record added.

4) When a new record is added, you just grab the next ID number in the
appropriate range (and for double caution look in the index to make sure
it hasn't been assigned already!).

--
Best wishes

John Nurick

e-mail: j.nur...@bbcnc.org.uk
compuserve: 70162,2472
voicemail: <+44|0> 956 579846
London, England

Re:Need advice - first major Database App


Hi,

You could set up a second field on each table that specifies the
location. This allows you to continue using the auto-increment field
while keeping data from different companies/places seperate.

Remember though... I am suggesting that your PRIMARY key is now two
fields: the auto-increment and the site id. That means that you will
have to store both fields in any table that references the table. Also
keep in mind that not ALL table will need this secondary field as part
of their primary key. A child table could still have a unique primary
key, if it incorporates some or all of its parent's primary key in its
primary key.

Good luck (hope this helps),
Allen

Quote
ecot...@unibase.unibase.com (Warren Sande) wrote:
>w...@sophocles.algonet.se (Johan Alveborg) wrote:
>>I would definitely break it down into several DB's. One for the
>>applicants general info (name/adress etc) and then perhaps try to categorize
>>the other info in some way, say one DB for financial info, one for
>>company info (type of business etc). Perhaps one DB for each of the 20
>>(or so) pages you mentioned. This also makes it faster for any queries
>>they would like to make for statistical purposes as well as preserving
>>resources when displaying an application and perhaps making it easier for
>>the user to select the information he/she is interested in and displaying
>>it in a separate window.
>Thanks for the advice.  I think I am going to do it this way.  But I
>have another question relating to this.  
>Obviously, I need a way to link the varoius tables.  So each table
>will have an "ID_NUMBER" field that I can use to join the tables.  I
>took a first run at this, and I made ID_NUMBER an Auto-Increment field
>in the main table, and a LongInteger field in all the other tables.
>So, when a new applicaton is started (a new record added), the main
>table sets the ID_NUMBER, then the I will have the other tables read
>that value and use the same ID_NUMBER.  No problem so far.
>But each applicant will have their own copy of the program running on
>their own system, with their own set of tables.  When they send in a
>disk with all the data, the funding administrators have to "import"
>the record into their copy of the program, with their own tables.
>Several applicants could have the same value for ID_NUMBER, since they
>were all created on different systems.  But the ID_NUMBERs must be
>unique when then the administrator puts all the applications on his
>system.  How should I handle this?  (I have some ideas, but I would
>like to hear your opinions.)
>Thanks for the assistance.

>Warren Sande
>EcoTech Research
>Regina, Saskatchewan, Canada
>ecot...@unibase.unibase.com

Other Threads