Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Keeping History

13 posts in Delphi Last posting was on 2003-09-17 05:30:01.0Z
Brian Posted on 2003-09-12 20:15:45.0Z
From: "Brian" <NoEmail@NoEmail.com>
Newsgroups: Advantage.Delphi
Subject: Keeping History
Date: Fri, 12 Sep 2003 15:15:45 -0500
Lines: 24
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4920.2300
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4920.2300
NNTP-Posting-Host: 216.152.27.55
Message-ID: <3f622a0d@solutions.advantagedatabase.com>
X-Trace: 12 Sep 2003 14:18:21 -0700, 216.152.27.55
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!216.152.27.55
Xref: solutions.advantagedatabase.com Advantage.Delphi:13941
Article PK: 1107378

Hello,

I am tring to decide a good way to design a database using Delphi, ADS, and
ADTs.

We have a two tables:

1. Master (name, address, year, etc)
2. Detail (personal inventory - sofa, chair, stove, etc)

What we would like to be able to do is to be able to go thru the database
for any year and list the items that the person owned on any given date.

They problem is that they only way I can come up with, involves duplication
each record at the beginning of the next year.

Anyone know of a way to accomplish this so that each record does not have to
be duplicated?

tia

Kevin


Karl Perry Posted on 2003-09-14 03:21:45.0Z
From: "Karl Perry" <kaperry@NOSPAMcablespeed.com>
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com>
Subject: Re: Keeping History
Date: Sat, 13 Sep 2003 20:21:45 -0700
Lines: 81
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 66.235.30.168
Message-ID: <3f63df9c@solutions.advantagedatabase.com>
X-Trace: 13 Sep 2003 21:25:16 -0700, 66.235.30.168
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.235.30.168
Xref: solutions.advantagedatabase.com Advantage.Delphi:13943
Article PK: 1107379

Kevin,

You need more than two tables.

You need a table to keep basic information about the person; call it
Clients.ADT: PersonID (a unique ID number that has no relation to anything
else - DON'T use a social security number or phone number - use an autoinc),
name, address, phone, e-mail address, date they became your client, etc.
Only information that pertains directly to the person him/herself (or
couple, if that's your business) should be included in this table. No
information about any furniture or items should be in this table.

You need a table of item types; call it ItemTypes.ADT: record 1 is
"Overstuffed chair", record 2 is "formal dining table", etc. Only
information about a specific kind/model/brand/etc. of furniture should be in
this table - think of it as a list of every type of item anyone could own.
Each item type should have a unique ID (call it ItemTypeID), probably just
an autoinc field. No information about anyone who owns one of these, or
about specific items owned by someone, should be in this table. It's merely
a place to store names and information about item types.

You need a table to link the above two tables; call it
ClientsItemTypesLink.ADT. This table will be where you store when a client
obtained each piece of furniture and when they disposed of it: CILinkID (an
autoinc field that makes every record in this table unique), Client ID,
ItemTypeID, SerialNumber (or other descriptor to allow you to have more than
one of each item type per client), DateObtained, DateDisposedOf (this may
well be blank), and other fields you want to include. Only information
specifically related to the specific piece of furniture owned by the
specific client should be stored as fields in this table.

Now - if you are tracking inventory where every single piece is unique, you
can get by with just two tables. In this case, you add the ClientID to the
ItemTypes.ADT table, eliminate the Link table, and add the DateObtained and
DateDisposedOf to the table.

You may have other tables too, depending on your interests/needs.

Once you've populated the database, you can use SQL statements to query it
any way you want. You would query the items table based on the year
desired, looking for DateObtained records with a year in or before the
target year and DateDisposedOf records either null (blank) or on or after
the target year.

HTH,

Karl Perry

"Brian" <NoEmail@NoEmail.com> wrote in message
news:3f622a0d@solutions.advantagedatabase.com...
> Hello,
>
> I am tring to decide a good way to design a database using Delphi, ADS,
and
> ADTs.
>
> We have a two tables:
>
> 1. Master (name, address, year, etc)
> 2. Detail (personal inventory - sofa, chair, stove, etc)
>
> What we would like to be able to do is to be able to go thru the database
> for any year and list the items that the person owned on any given date.
>
> They problem is that they only way I can come up with, involves
duplication
> each record at the beginning of the next year.
>
> Anyone know of a way to accomplish this so that each record does not have
to
> be duplicated?
>
> tia
>
> Kevin
>
>


Kevin B. Posted on 2003-09-14 03:39:07.0Z
From: "Kevin B." <NoEmail@NoEmail.com>
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com> <3f63df9c@solutions.advantagedatabase.com>
Subject: Re: Keeping History
Date: Sat, 13 Sep 2003 22:39:07 -0500
Lines: 110
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 66.76.222.96
Message-ID: <3f63e346@solutions.advantagedatabase.com>
X-Trace: 13 Sep 2003 21:40:54 -0700, 66.76.222.96
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.76.222.96
Xref: solutions.advantagedatabase.com Advantage.Delphi:13945
Article PK: 1107381

Hi Carl,

Thanks for the reply. That helps. I don't think I was very clear on our
other requirment. That is, we need to track changes. For example, people
move, trade old items for newer ones, etc. We would like to be able to
query the database and be able to show where the person lived, what items
that had at that time, for any given date. That means that we will have to
keep some sort of history file(s) (I think). I would like to be able to do
it without HistoryFiles, but I don't think that it is possible.

Regards

Kevin

"Karl Perry" <kaperry@NOSPAMcablespeed.com> wrote in message
news:3f63df9c@solutions.advantagedatabase.com...
> Kevin,
>
> You need more than two tables.
>
> You need a table to keep basic information about the person; call it
> Clients.ADT: PersonID (a unique ID number that has no relation to anything
> else - DON'T use a social security number or phone number - use an
autoinc),
> name, address, phone, e-mail address, date they became your client, etc.
> Only information that pertains directly to the person him/herself (or
> couple, if that's your business) should be included in this table. No
> information about any furniture or items should be in this table.
>
> You need a table of item types; call it ItemTypes.ADT: record 1 is
> "Overstuffed chair", record 2 is "formal dining table", etc. Only
> information about a specific kind/model/brand/etc. of furniture should be
in
> this table - think of it as a list of every type of item anyone could own.
> Each item type should have a unique ID (call it ItemTypeID), probably just
> an autoinc field. No information about anyone who owns one of these, or
> about specific items owned by someone, should be in this table. It's
merely
> a place to store names and information about item types.
>
> You need a table to link the above two tables; call it
> ClientsItemTypesLink.ADT. This table will be where you store when a
client
> obtained each piece of furniture and when they disposed of it: CILinkID
(an
> autoinc field that makes every record in this table unique), Client ID,
> ItemTypeID, SerialNumber (or other descriptor to allow you to have more
than
> one of each item type per client), DateObtained, DateDisposedOf (this may
> well be blank), and other fields you want to include. Only information
> specifically related to the specific piece of furniture owned by the
> specific client should be stored as fields in this table.
>
> Now - if you are tracking inventory where every single piece is unique,
you
> can get by with just two tables. In this case, you add the ClientID to
the
> ItemTypes.ADT table, eliminate the Link table, and add the DateObtained
and
> DateDisposedOf to the table.
>
> You may have other tables too, depending on your interests/needs.
>
> Once you've populated the database, you can use SQL statements to query it
> any way you want. You would query the items table based on the year
> desired, looking for DateObtained records with a year in or before the
> target year and DateDisposedOf records either null (blank) or on or after
> the target year.
>
> HTH,
>
> Karl Perry
>
>
>
> "Brian" <NoEmail@NoEmail.com> wrote in message
> news:3f622a0d@solutions.advantagedatabase.com...
> > Hello,
> >
> > I am tring to decide a good way to design a database using Delphi, ADS,
> and
> > ADTs.
> >
> > We have a two tables:
> >
> > 1. Master (name, address, year, etc)
> > 2. Detail (personal inventory - sofa, chair, stove, etc)
> >
> > What we would like to be able to do is to be able to go thru the
database
> > for any year and list the items that the person owned on any given date.
> >
> > They problem is that they only way I can come up with, involves
> duplication
> > each record at the beginning of the next year.
> >
> > Anyone know of a way to accomplish this so that each record does not
have
> to
> > be duplicated?
> >
> > tia
> >
> > Kevin
> >
> >
>
>


Karl Perry Posted on 2003-09-14 15:56:20.0Z
From: "Karl Perry" <kaperry@NOSPAMcablespeed.com>
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com> <3f63df9c@solutions.advantagedatabase.com> <3f63e346@solutions.advantagedatabase.com>
Subject: Re: Keeping History
Date: Sun, 14 Sep 2003 08:56:20 -0700
Lines: 160
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 66.235.30.168
Message-ID: <3f64908b@solutions.advantagedatabase.com>
X-Trace: 14 Sep 2003 10:00:11 -0700, 66.235.30.168
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.235.30.168
Xref: solutions.advantagedatabase.com Advantage.Delphi:13946
Article PK: 1107383

Hi, Cevin/Bhryian -

Without an accurate, complete description of what you are trying to
accomplish it's hard to give you good advice. However, it seems to me like
you have a rental-type app: you have a number of items, and a number of
people who may possess them one-by-one for a period of time. Is this right?

So, you still have a Clients.ADT table with nothing but information that
directly pertains to the client, as I described earlier. What I'm talking
about here is called database normalization. One of the rules is that in
any given table, the only information in that table is what pertains to the
table's primary purpose. If the purpose of a table is to store client
information, then you don't store information about when they started
renting a particular chair.

You will also need a table of inventory items. This table will have
information about each of the rental items you have in your stock. There is
no information in the table about who is renting an item at any given time,
though you could have a status field that indicates whether or not an item
is out for rent "right now."

You will need a third table that keeps track of the changes you mentioned.
This is a ClientsItemsLink table. Here, you have the ID of the client, the
ID of the item, the date the item was rented to the client and the date the
item was returned to your stock (or it's blank because it's still on rent to
that client).

With this structure you can do everything you need.

For more than this, you really need the services of a database professional.
If you're interested, contact me via e-mail.

Karl

"Kevin B." <NoEmail@NoEmail.com> wrote in message
news:3f63e346@solutions.advantagedatabase.com...
> Hi Carl,
>
> Thanks for the reply. That helps. I don't think I was very clear on our
> other requirment. That is, we need to track changes. For example,
people
> move, trade old items for newer ones, etc. We would like to be able to
> query the database and be able to show where the person lived, what items
> that had at that time, for any given date. That means that we will have
to
> keep some sort of history file(s) (I think). I would like to be able to
do
> it without HistoryFiles, but I don't think that it is possible.
>
> Regards
>
> Kevin
>
> "Karl Perry" <kaperry@NOSPAMcablespeed.com> wrote in message
> news:3f63df9c@solutions.advantagedatabase.com...
> > Kevin,
> >
> > You need more than two tables.
> >
> > You need a table to keep basic information about the person; call it
> > Clients.ADT: PersonID (a unique ID number that has no relation to
anything
> > else - DON'T use a social security number or phone number - use an
> autoinc),
> > name, address, phone, e-mail address, date they became your client, etc.
> > Only information that pertains directly to the person him/herself (or
> > couple, if that's your business) should be included in this table. No
> > information about any furniture or items should be in this table.
> >
> > You need a table of item types; call it ItemTypes.ADT: record 1 is
> > "Overstuffed chair", record 2 is "formal dining table", etc. Only
> > information about a specific kind/model/brand/etc. of furniture should
be
> in
> > this table - think of it as a list of every type of item anyone could
own.
> > Each item type should have a unique ID (call it ItemTypeID), probably
just
> > an autoinc field. No information about anyone who owns one of these, or
> > about specific items owned by someone, should be in this table. It's
> merely
> > a place to store names and information about item types.
> >
> > You need a table to link the above two tables; call it
> > ClientsItemTypesLink.ADT. This table will be where you store when a
> client
> > obtained each piece of furniture and when they disposed of it: CILinkID
> (an
> > autoinc field that makes every record in this table unique), Client ID,
> > ItemTypeID, SerialNumber (or other descriptor to allow you to have more
> than
> > one of each item type per client), DateObtained, DateDisposedOf (this
may
> > well be blank), and other fields you want to include. Only information
> > specifically related to the specific piece of furniture owned by the
> > specific client should be stored as fields in this table.
> >
> > Now - if you are tracking inventory where every single piece is unique,
> you
> > can get by with just two tables. In this case, you add the ClientID to
> the
> > ItemTypes.ADT table, eliminate the Link table, and add the DateObtained
> and
> > DateDisposedOf to the table.
> >
> > You may have other tables too, depending on your interests/needs.
> >
> > Once you've populated the database, you can use SQL statements to query
it
> > any way you want. You would query the items table based on the year
> > desired, looking for DateObtained records with a year in or before the
> > target year and DateDisposedOf records either null (blank) or on or
after
> > the target year.
> >
> > HTH,
> >
> > Karl Perry
> >
> >
> >
> > "Brian" <NoEmail@NoEmail.com> wrote in message
> > news:3f622a0d@solutions.advantagedatabase.com...
> > > Hello,
> > >
> > > I am tring to decide a good way to design a database using Delphi,
ADS,
> > and
> > > ADTs.
> > >
> > > We have a two tables:
> > >
> > > 1. Master (name, address, year, etc)
> > > 2. Detail (personal inventory - sofa, chair, stove, etc)
> > >
> > > What we would like to be able to do is to be able to go thru the
> database
> > > for any year and list the items that the person owned on any given
date.
> > >
> > > They problem is that they only way I can come up with, involves
> > duplication
> > > each record at the beginning of the next year.
> > >
> > > Anyone know of a way to accomplish this so that each record does not
> have
> > to
> > > be duplicated?
> > >
> > > tia
> > >
> > > Kevin
> > >
> > >
> >
> >
>
>


Kevin B. Posted on 2003-09-15 01:42:25.0Z
From: "Kevin B." <NoEmail@NoEmail.com>
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com> <3f63df9c@solutions.advantagedatabase.com> <3f63e346@solutions.advantagedatabase.com> <3f64908b@solutions.advantagedatabase.com>
Subject: Re: Keeping History
Date: Sun, 14 Sep 2003 20:42:25 -0500
Lines: 207
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 66.76.222.96
Message-ID: <3f65198e@solutions.advantagedatabase.com>
X-Trace: 14 Sep 2003 19:44:46 -0700, 66.76.222.96
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.76.222.96
Xref: solutions.advantagedatabase.com Advantage.Delphi:13947
Article PK: 1107384

Hello Karl,

Thanks bunches for your advice. The one thing that I an not sure of is how
to track client history. For example, as I mentioned previously, we want to
be able to look back on any date and determine what peice of furniture the
customer had, his address, phone number, etc. Would you suggest just
cloning the Client table each time a change to his record is made or would
there be a better way. We have been discussing two possibilities:

1. Duplicate the record in the current Client table and set the StatusFlag
to "old".

2. Create a ClientHistory table that is the same structure as Client.ADT.
Move the old record to it whenever any change to the client's data is
changed.

3. Could there be a third approach?

Regards,

Kevin

"Karl Perry" <kaperry@NOSPAMcablespeed.com> wrote in message
news:3f64908b@solutions.advantagedatabase.com...
> Hi, Cevin/Bhryian -
>
> Without an accurate, complete description of what you are trying to
> accomplish it's hard to give you good advice. However, it seems to me
like
> you have a rental-type app: you have a number of items, and a number of
> people who may possess them one-by-one for a period of time. Is this
right?
>
> So, you still have a Clients.ADT table with nothing but information that
> directly pertains to the client, as I described earlier. What I'm talking
> about here is called database normalization. One of the rules is that in
> any given table, the only information in that table is what pertains to
the
> table's primary purpose. If the purpose of a table is to store client
> information, then you don't store information about when they started
> renting a particular chair.
>
> You will also need a table of inventory items. This table will have
> information about each of the rental items you have in your stock. There
is
> no information in the table about who is renting an item at any given
time,
> though you could have a status field that indicates whether or not an item
> is out for rent "right now."
>
> You will need a third table that keeps track of the changes you mentioned.
> This is a ClientsItemsLink table. Here, you have the ID of the client,
the
> ID of the item, the date the item was rented to the client and the date
the
> item was returned to your stock (or it's blank because it's still on rent
to
> that client).
>
> With this structure you can do everything you need.
>
> For more than this, you really need the services of a database
professional.
> If you're interested, contact me via e-mail.
>
> Karl
>
> "Kevin B." <NoEmail@NoEmail.com> wrote in message
> news:3f63e346@solutions.advantagedatabase.com...
> > Hi Carl,
> >
> > Thanks for the reply. That helps. I don't think I was very clear on
our
> > other requirment. That is, we need to track changes. For example,
> people
> > move, trade old items for newer ones, etc. We would like to be able to
> > query the database and be able to show where the person lived, what
items
> > that had at that time, for any given date. That means that we will have
> to
> > keep some sort of history file(s) (I think). I would like to be able to
> do
> > it without HistoryFiles, but I don't think that it is possible.
> >
> > Regards
> >
> > Kevin
> >
> > "Karl Perry" <kaperry@NOSPAMcablespeed.com> wrote in message
> > news:3f63df9c@solutions.advantagedatabase.com...
> > > Kevin,
> > >
> > > You need more than two tables.
> > >
> > > You need a table to keep basic information about the person; call it
> > > Clients.ADT: PersonID (a unique ID number that has no relation to
> anything
> > > else - DON'T use a social security number or phone number - use an
> > autoinc),
> > > name, address, phone, e-mail address, date they became your client,
etc.
> > > Only information that pertains directly to the person him/herself (or
> > > couple, if that's your business) should be included in this table. No
> > > information about any furniture or items should be in this table.
> > >
> > > You need a table of item types; call it ItemTypes.ADT: record 1 is
> > > "Overstuffed chair", record 2 is "formal dining table", etc. Only
> > > information about a specific kind/model/brand/etc. of furniture should
> be
> > in
> > > this table - think of it as a list of every type of item anyone could
> own.
> > > Each item type should have a unique ID (call it ItemTypeID), probably
> just
> > > an autoinc field. No information about anyone who owns one of these,
or
> > > about specific items owned by someone, should be in this table. It's
> > merely
> > > a place to store names and information about item types.
> > >
> > > You need a table to link the above two tables; call it
> > > ClientsItemTypesLink.ADT. This table will be where you store when a
> > client
> > > obtained each piece of furniture and when they disposed of it:
CILinkID
> > (an
> > > autoinc field that makes every record in this table unique), Client
ID,
> > > ItemTypeID, SerialNumber (or other descriptor to allow you to have
more
> > than
> > > one of each item type per client), DateObtained, DateDisposedOf (this
> may
> > > well be blank), and other fields you want to include. Only
information
> > > specifically related to the specific piece of furniture owned by the
> > > specific client should be stored as fields in this table.
> > >
> > > Now - if you are tracking inventory where every single piece is
unique,
> > you
> > > can get by with just two tables. In this case, you add the ClientID
to
> > the
> > > ItemTypes.ADT table, eliminate the Link table, and add the
DateObtained
> > and
> > > DateDisposedOf to the table.
> > >
> > > You may have other tables too, depending on your interests/needs.
> > >
> > > Once you've populated the database, you can use SQL statements to
query
> it
> > > any way you want. You would query the items table based on the year
> > > desired, looking for DateObtained records with a year in or before the
> > > target year and DateDisposedOf records either null (blank) or on or
> after
> > > the target year.
> > >
> > > HTH,
> > >
> > > Karl Perry
> > >
> > >
> > >
> > > "Brian" <NoEmail@NoEmail.com> wrote in message
> > > news:3f622a0d@solutions.advantagedatabase.com...
> > > > Hello,
> > > >
> > > > I am tring to decide a good way to design a database using Delphi,
> ADS,
> > > and
> > > > ADTs.
> > > >
> > > > We have a two tables:
> > > >
> > > > 1. Master (name, address, year, etc)
> > > > 2. Detail (personal inventory - sofa, chair, stove, etc)
> > > >
> > > > What we would like to be able to do is to be able to go thru the
> > database
> > > > for any year and list the items that the person owned on any given
> date.
> > > >
> > > > They problem is that they only way I can come up with, involves
> > > duplication
> > > > each record at the beginning of the next year.
> > > >
> > > > Anyone know of a way to accomplish this so that each record does not
> > have
> > > to
> > > > be duplicated?
> > > >
> > > > tia
> > > >
> > > > Kevin
> > > >
> > > >
> > >
> > >
> >
> >
>
>


Karl Perry Posted on 2003-09-15 13:51:26.0Z
From: "Karl Perry" <kaperry@NOSPAMcablespeed.com>
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com> <3f63df9c@solutions.advantagedatabase.com> <3f63e346@solutions.advantagedatabase.com> <3f64908b@solutions.advantagedatabase.com> <3f65198e@solutions.advantagedatabase.com>
Subject: Re: Keeping History
Date: Mon, 15 Sep 2003 06:51:26 -0700
Lines: 48
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 64.122.99.34
Message-ID: <3f65c4ab@solutions.advantagedatabase.com>
X-Trace: 15 Sep 2003 07:54:51 -0700, 64.122.99.34
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!64.122.99.34
Xref: solutions.advantagedatabase.com Advantage.Delphi:13950
Article PK: 1107386


"Kevin B." <NoEmail@NoEmail.com> wrote in message
news:3f65198e@solutions.advantagedatabase.com...
> Hello Karl,
>
> Thanks bunches for your advice. The one thing that I an not sure of is
how
> to track client history. For example, as I mentioned previously, we want
to
> be able to look back on any date and determine what peice of furniture the
> customer had, his address, phone number, etc. Would you suggest just
> cloning the Client table each time a change to his record is made or would
> there be a better way. We have been discussing two possibilities:
>
> 1. Duplicate the record in the current Client table and set the StatusFlag
> to "old".
>
> 2. Create a ClientHistory table that is the same structure as Client.ADT.
> Move the old record to it whenever any change to the client's data is
> changed.
>
> 3. Could there be a third approach?

Kevin,

You don't want to duplicate anything. Don't duplicate records. Don't make
duplicates of any records. Duplicating records will make life harder on you
than if you didn't duplicate records.

Don't make a history table. This will make life harder on you.

With the structure I suggested in previous posts, you can query the database
at any time, for any time period, and determine which items are in a
client's possession on any given date.

You really need the services of a qualified database professional to get
your project off on the right track. Look in your Yellow Pages under
Computers - software and services and interview a bunch of people, telling
them that you want help and mentoring in developing your own software
system. You should be able to find someone who will be willing to help you
on an hourly basis to do this.

If I have time tonight after work, I'll put together a really simple example
that will help illustrate what I'm talking about.

Karl


Kevin Ray Posted on 2003-09-15 14:15:14.0Z
From: "Kevin Ray" <NoEmail@NoEmail.com>
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com> <3f63df9c@solutions.advantagedatabase.com> <3f63e346@solutions.advantagedatabase.com> <3f64908b@solutions.advantagedatabase.com> <3f65198e@solutions.advantagedatabase.com> <3f65c4ab@solutions.advantagedatabase.com>
Subject: Re: Keeping History
Date: Mon, 15 Sep 2003 09:15:14 -0500
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4920.2300
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4920.2300
NNTP-Posting-Host: 216.152.27.55
Message-ID: <3f65ca1f@solutions.advantagedatabase.com>
X-Trace: 15 Sep 2003 08:18:07 -0700, 216.152.27.55
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!216.152.27.55
Xref: solutions.advantagedatabase.com Advantage.Delphi:13951
Article PK: 1107387

Hi Karl,

Thanks for your input and your interest.

>
> You don't want to duplicate anything. Don't duplicate records. Don't
make
> duplicates of any records. Duplicating records will make life harder on
you
> than if you didn't duplicate records.
>
> Don't make a history table. This will make life harder on you.
>
> With the structure I suggested in previous posts, you can query the
database
> at any time, for any time period, and determine which items are in a
> client's possession on any given date.

We also want to know which items he had in the past, and were he lived when
he rented them, etc.
So, a History table seemed like the right thing to do. We want to be able
to pick any date and see what he is renting now and where he lives, and what
he rented in the past and where he lived at that time. You see, if he comes
in next month and wants to rent a sofa and he has moved, we want to keep he
old address (as well as other information) in the system

Regards,

Kevin


Fred Williams Posted on 2003-09-16 15:17:57.0Z
From: "Fred Williams" <nsf.williams@verizon.net>
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com> <3f63df9c@solutions.advantagedatabase.com> <3f63e346@solutions.advantagedatabase.com> <3f64908b@solutions.advantagedatabase.com> <3f65198e@solutions.advantagedatabase.com> <3f65c4ab@solutions.advantagedatabase.com> <3f65ca1f@solutions.advantagedatabase.com>
Subject: Re: Keeping History
Date: Tue, 16 Sep 2003 10:17:57 -0500
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 4.65.171.238
Message-ID: <3f672a14@solutions.advantagedatabase.com>
X-Trace: 16 Sep 2003 09:19:48 -0700, 4.65.171.238
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!4.65.171.238
Xref: solutions.advantagedatabase.com Advantage.Delphi:13955
Article PK: 1107392

You need at least three related files:

Client
Location
Rental Items

Therefore a Client can have multi locations (past and present) and multi
items also past and present. All should have a begining and ending date.

"Kevin Ray" <NoEmail@NoEmail.com> wrote in message
news:3f65ca1f@solutions.advantagedatabase.com...
> Hi Karl,
>
> Thanks for your input and your interest.
>
> >
> > You don't want to duplicate anything. Don't duplicate records. Don't
> make
> > duplicates of any records. Duplicating records will make life harder on
> you
> > than if you didn't duplicate records.
> >
> > Don't make a history table. This will make life harder on you.
> >
> > With the structure I suggested in previous posts, you can query the
> database
> > at any time, for any time period, and determine which items are in a
> > client's possession on any given date.
>
> We also want to know which items he had in the past, and were he lived
when
> he rented them, etc.
> So, a History table seemed like the right thing to do. We want to be able
> to pick any date and see what he is renting now and where he lives, and
what
> he rented in the past and where he lived at that time. You see, if he
comes
> in next month and wants to rent a sofa and he has moved, we want to keep
he
> old address (as well as other information) in the system
>
> Regards,
>
> Kevin
>
>
>


Kevin Ray Posted on 2003-09-16 17:42:31.0Z
From: "Kevin Ray" <NoEmail@NoEmail.com>
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com> <3f63df9c@solutions.advantagedatabase.com> <3f63e346@solutions.advantagedatabase.com> <3f64908b@solutions.advantagedatabase.com> <3f65198e@solutions.advantagedatabase.com> <3f65c4ab@solutions.advantagedatabase.com> <3f65ca1f@solutions.advantagedatabase.com> <3f672a14@solutions.advantagedatabase.com>
Subject: Re: Keeping History
Date: Tue, 16 Sep 2003 12:42:31 -0500
Lines: 66
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4920.2300
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4920.2300
NNTP-Posting-Host: 216.152.27.55
Message-ID: <3f674c65@solutions.advantagedatabase.com>
X-Trace: 16 Sep 2003 11:46:13 -0700, 216.152.27.55
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!216.152.27.55
Xref: solutions.advantagedatabase.com Advantage.Delphi:13956
Article PK: 1107391

Hello Fred,

How would adding a Location field aid me in tracking his past addresses and
rental history?

Regards


Kevin

"Fred Williams" <nsf.williams@verizon.net> wrote in message
news:3f672a14@solutions.advantagedatabase.com...
> You need at least three related files:
>
> Client
> Location
> Rental Items
>
> Therefore a Client can have multi locations (past and present) and multi
> items also past and present. All should have a begining and ending date.
>
> "Kevin Ray" <NoEmail@NoEmail.com> wrote in message
> news:3f65ca1f@solutions.advantagedatabase.com...
> > Hi Karl,
> >
> > Thanks for your input and your interest.
> >
> > >
> > > You don't want to duplicate anything. Don't duplicate records. Don't
> > make
> > > duplicates of any records. Duplicating records will make life harder
on
> > you
> > > than if you didn't duplicate records.
> > >
> > > Don't make a history table. This will make life harder on you.
> > >
> > > With the structure I suggested in previous posts, you can query the
> > database
> > > at any time, for any time period, and determine which items are in a
> > > client's possession on any given date.
> >
> > We also want to know which items he had in the past, and were he lived
> when
> > he rented them, etc.
> > So, a History table seemed like the right thing to do. We want to be
able
> > to pick any date and see what he is renting now and where he lives, and
> what
> > he rented in the past and where he lived at that time. You see, if he
> comes
> > in next month and wants to rent a sofa and he has moved, we want to keep
> he
> > old address (as well as other information) in the system
> >
> > Regards,
> >
> > Kevin
> >
> >
> >
>
>


Fred Williams Posted on 2003-09-17 01:18:06.0Z
From: "Fred Williams" <nsf.williams@verizon.net>
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com> <3f63df9c@solutions.advantagedatabase.com> <3f63e346@solutions.advantagedatabase.com> <3f64908b@solutions.advantagedatabase.com> <3f65198e@solutions.advantagedatabase.com> <3f65c4ab@solutions.advantagedatabase.com> <3f65ca1f@solutions.advantagedatabase.com> <3f672a14@solutions.advantagedatabase.com> <3f674c65@solutions.advantagedatabase.com>
Subject: Re: Keeping History
Date: Tue, 16 Sep 2003 20:18:06 -0500
Lines: 96
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 4.65.171.238
Message-ID: <3f67b6ce@solutions.advantagedatabase.com>
X-Trace: 16 Sep 2003 19:20:14 -0700, 4.65.171.238
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!4.65.171.238
Xref: solutions.advantagedatabase.com Advantage.Delphi:13958
Article PK: 1107393

Well your file (table) skeletons should be:

Client: ClientID (Info directly related to client. i.e. Name, First
Rental Date, and etc)

Location: ClientID + LocationID (Info directly related to Location i.e.
Street address, Property type, Move In Date, Move Out Date, and etc.) This
could be done with a separate Location Table, but this stream lines things
somewhat.

Rental Item: ClientID + ItemID (Info directly related to an Item rented by
the client. i.e. Description, Rental Date, Return Date, and etc.) This too
could be a separate Item table for greater flexability.

Not truly first normal, but reduces complexity by restricting flexability.
For a very large application one should strive for true first normal design
and accept the increased comlpexity.

Fred

"Kevin Ray" <NoEmail@NoEmail.com> wrote in message
news:3f674c65@solutions.advantagedatabase.com...
> Hello Fred,
>
> How would adding a Location field aid me in tracking his past addresses
and
> rental history?
>
> Regards
>
>
> Kevin
>
>
> "Fred Williams" <nsf.williams@verizon.net> wrote in message
> news:3f672a14@solutions.advantagedatabase.com...
> > You need at least three related files:
> >
> > Client
> > Location
> > Rental Items
> >
> > Therefore a Client can have multi locations (past and present) and multi
> > items also past and present. All should have a begining and ending
date.
> >
> > "Kevin Ray" <NoEmail@NoEmail.com> wrote in message
> > news:3f65ca1f@solutions.advantagedatabase.com...
> > > Hi Karl,
> > >
> > > Thanks for your input and your interest.
> > >
> > > >
> > > > You don't want to duplicate anything. Don't duplicate records.
Don't
> > > make
> > > > duplicates of any records. Duplicating records will make life
harder
> on
> > > you
> > > > than if you didn't duplicate records.
> > > >
> > > > Don't make a history table. This will make life harder on you.
> > > >
> > > > With the structure I suggested in previous posts, you can query the
> > > database
> > > > at any time, for any time period, and determine which items are in a
> > > > client's possession on any given date.
> > >
> > > We also want to know which items he had in the past, and were he lived
> > when
> > > he rented them, etc.
> > > So, a History table seemed like the right thing to do. We want to be
> able
> > > to pick any date and see what he is renting now and where he lives,
and
> > what
> > > he rented in the past and where he lived at that time. You see, if he
> > comes
> > > in next month and wants to rent a sofa and he has moved, we want to
keep
> > he
> > > old address (as well as other information) in the system
> > >
> > > Regards,
> > >
> > > Kevin
> > >
> > >
> > >
> >
> >
>
>


Kevin B. Posted on 2003-09-17 01:37:53.0Z
From: "Kevin B." <NoEmail@NoEmail.com>
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com> <3f63df9c@solutions.advantagedatabase.com> <3f63e346@solutions.advantagedatabase.com> <3f64908b@solutions.advantagedatabase.com> <3f65198e@solutions.advantagedatabase.com> <3f65c4ab@solutions.advantagedatabase.com> <3f65ca1f@solutions.advantagedatabase.com> <3f672a14@solutions.advantagedatabase.com> <3f674c65@solutions.advantagedatabase.com> <3f67b6ce@solutions.advantagedatabase.com>
Subject: Re: Keeping History
Date: Tue, 16 Sep 2003 20:37:53 -0500
Lines: 117
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 66.76.222.96
Message-ID: <3f67bb56@solutions.advantagedatabase.com>
X-Trace: 16 Sep 2003 19:39:34 -0700, 66.76.222.96
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.76.222.96
Xref: solutions.advantagedatabase.com Advantage.Delphi:13959
Article PK: 1107395

Thank you all so much. You have been a big help to me.

Regards,

Kevin.

"Fred Williams" <nsf.williams@verizon.net> wrote in message
news:3f67b6ce@solutions.advantagedatabase.com...
> Well your file (table) skeletons should be:
>
> Client: ClientID (Info directly related to client. i.e. Name,
First
> Rental Date, and etc)
>
> Location: ClientID + LocationID (Info directly related to Location
i.e.
> Street address, Property type, Move In Date, Move Out Date, and etc.)
This
> could be done with a separate Location Table, but this stream lines things
> somewhat.
>
> Rental Item: ClientID + ItemID (Info directly related to an Item rented by
> the client. i.e. Description, Rental Date, Return Date, and etc.) This
too
> could be a separate Item table for greater flexability.
>
> Not truly first normal, but reduces complexity by restricting flexability.
> For a very large application one should strive for true first normal
design
> and accept the increased comlpexity.
>
> Fred
>
> "Kevin Ray" <NoEmail@NoEmail.com> wrote in message
> news:3f674c65@solutions.advantagedatabase.com...
> > Hello Fred,
> >
> > How would adding a Location field aid me in tracking his past addresses
> and
> > rental history?
> >
> > Regards
> >
> >
> > Kevin
> >
> >
> > "Fred Williams" <nsf.williams@verizon.net> wrote in message
> > news:3f672a14@solutions.advantagedatabase.com...
> > > You need at least three related files:
> > >
> > > Client
> > > Location
> > > Rental Items
> > >
> > > Therefore a Client can have multi locations (past and present) and
multi
> > > items also past and present. All should have a begining and ending
> date.
> > >
> > > "Kevin Ray" <NoEmail@NoEmail.com> wrote in message
> > > news:3f65ca1f@solutions.advantagedatabase.com...
> > > > Hi Karl,
> > > >
> > > > Thanks for your input and your interest.
> > > >
> > > > >
> > > > > You don't want to duplicate anything. Don't duplicate records.
> Don't
> > > > make
> > > > > duplicates of any records. Duplicating records will make life
> harder
> > on
> > > > you
> > > > > than if you didn't duplicate records.
> > > > >
> > > > > Don't make a history table. This will make life harder on you.
> > > > >
> > > > > With the structure I suggested in previous posts, you can query
the
> > > > database
> > > > > at any time, for any time period, and determine which items are in
a
> > > > > client's possession on any given date.
> > > >
> > > > We also want to know which items he had in the past, and were he
lived
> > > when
> > > > he rented them, etc.
> > > > So, a History table seemed like the right thing to do. We want to
be
> > able
> > > > to pick any date and see what he is renting now and where he lives,
> and
> > > what
> > > > he rented in the past and where he lived at that time. You see, if
he
> > > comes
> > > > in next month and wants to rent a sofa and he has moved, we want to
> keep
> > > he
> > > > old address (as well as other information) in the system
> > > >
> > > > Regards,
> > > >
> > > > Kevin
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


francois Posted on 2003-09-17 00:26:30.0Z
Reply-To: "francois" <fransh_@westnet.com.au>
From: "francois" <fransh_@westnet.com.au>
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com> <3f63df9c@solutions.advantagedatabase.com> <3f63e346@solutions.advantagedatabase.com> <3f64908b@solutions.advantagedatabase.com> <3f65198e@solutions.advantagedatabase.com> <3f65c4ab@solutions.advantagedatabase.com> <3f65ca1f@solutions.advantagedatabase.com> <3f672a14@solutions.advantagedatabase.com> <3f674c65@solutions.advantagedatabase.com>
Subject: Re: Keeping History
Date: Wed, 17 Sep 2003 08:26:30 +0800
Lines: 86
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4922.1500
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4925.2800
NNTP-Posting-Host: 202.72.180.11
Message-ID: <3f67ab7a@solutions.advantagedatabase.com>
X-Trace: 16 Sep 2003 18:31:54 -0700, 202.72.180.11
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!202.72.180.11
Xref: solutions.advantagedatabase.com Advantage.Delphi:13957
Article PK: 1107394

Hi
Once you have the tables created and played around with them in the Data
Architect you'll figure out how to create SQL queries that gives you exactly
that. All suggestions so far is in line with how you should tackle the
problem. If you cannot get that far by yourself you really need a
consultant. Why not just start and see. If you wait until you understand
everything you might not start at all.
HTH
Francois

"Kevin Ray" <NoEmail@NoEmail.com> wrote in message
news:3f674c65@solutions.advantagedatabase.com...
> Hello Fred,
>
> How would adding a Location field aid me in tracking his past addresses
and
> rental history?
>
> Regards
>
>
> Kevin
>
>
> "Fred Williams" <nsf.williams@verizon.net> wrote in message
> news:3f672a14@solutions.advantagedatabase.com...
> > You need at least three related files:
> >
> > Client
> > Location
> > Rental Items
> >
> > Therefore a Client can have multi locations (past and present) and multi
> > items also past and present. All should have a begining and ending
date.
> >
> > "Kevin Ray" <NoEmail@NoEmail.com> wrote in message
> > news:3f65ca1f@solutions.advantagedatabase.com...
> > > Hi Karl,
> > >
> > > Thanks for your input and your interest.
> > >
> > > >
> > > > You don't want to duplicate anything. Don't duplicate records.
Don't
> > > make
> > > > duplicates of any records. Duplicating records will make life
harder
> on
> > > you
> > > > than if you didn't duplicate records.
> > > >
> > > > Don't make a history table. This will make life harder on you.
> > > >
> > > > With the structure I suggested in previous posts, you can query the
> > > database
> > > > at any time, for any time period, and determine which items are in a
> > > > client's possession on any given date.
> > >
> > > We also want to know which items he had in the past, and were he lived
> > when
> > > he rented them, etc.
> > > So, a History table seemed like the right thing to do. We want to be
> able
> > > to pick any date and see what he is renting now and where he lives,
and
> > what
> > > he rented in the past and where he lived at that time. You see, if he
> > comes
> > > in next month and wants to rent a sofa and he has moved, we want to
keep
> > he
> > > old address (as well as other information) in the system
> > >
> > > Regards,
> > >
> > > Kevin
> > >
> > >
> > >
> >
> >
>
>


"Jan Ferguson" Posted on 2003-09-17 05:30:01.0Z
From: "Jan Ferguson" <(See sig line, if applicable)>
Subject: Re: Keeping History
Newsgroups: Advantage.Delphi
References: <3f622a0d@solutions.advantagedatabase.com> <3f63df9c@solutions.advantagedatabase.com> <3f63e346@solutions.advantagedatabase.com> <3f64908b@solutions.advantagedatabase.com> <3f65198e@solutions.advantagedatabase.com> <3f65c4ab@solutions.advantagedatabase.com> <3f65ca1f@solutions.advantagedatabase.com> <3f672a14@solutions.advantagedatabase.com> <3f674c65@solutions.advantagedatabase.com>
User-Agent: XanaNews/1.15.6.3
NNTP-Posting-Host: 66.176.99.235
Message-ID: <3f67e349@solutions.advantagedatabase.com>
Date: 16 Sep 2003 22:30:01 -0700
X-Trace: 16 Sep 2003 22:30:01 -0700, 66.176.99.235
Lines: 84
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.176.99.235
Xref: solutions.advantagedatabase.com Advantage.Delphi:13960
Article PK: 1107396

Kevin,

He said you need a Location *file*, not a Location "field". A file
would definitely help you do what you need to.

Jan Ferguson

Kevin Ray wrote:

> Hello Fred,
>
> How would adding a Location field aid me in tracking his past
> addresses and rental history?
>
> Regards
>
>
> Kevin
>
>
> "Fred Williams" <nsf.williams@verizon.net> wrote in message
> news:3f672a14@solutions.advantagedatabase.com...
> > You need at least three related files:
> >
> > Client
> > Location
> > Rental Items
> >
> > Therefore a Client can have multi locations (past and present) and
> > multi items also past and present. All should have a begining and
> > ending date.
> >
> > "Kevin Ray" <NoEmail@NoEmail.com> wrote in message
> > news:3f65ca1f@solutions.advantagedatabase.com...
> > > Hi Karl,
> > >
> > > Thanks for your input and your interest.
> > >
> > > >
> > > > You don't want to duplicate anything. Don't duplicate records.
> > > > Don't
> > > make
> > > > duplicates of any records. Duplicating records will make life
> > > > harder
> on
> > > you
> > > > than if you didn't duplicate records.
> > > >
> > > > Don't make a history table. This will make life harder on you.
> > > >
> > > > With the structure I suggested in previous posts, you can query
> > > > the
> > > database
> > > > at any time, for any time period, and determine which items are
> > > > in a client's possession on any given date.
> > >
> > > We also want to know which items he had in the past, and were he
> > > lived
> > when
> > > he rented them, etc.
> > > So, a History table seemed like the right thing to do. We want
> > > to be
> able
> > > to pick any date and see what he is renting now and where he
> > > lives, and
> > what
> > > he rented in the past and where he lived at that time. You see,
> > > if he
> > comes
> > > in next month and wants to rent a sofa and he has moved, we want
> > > to keep
> > he
> > > old address (as well as other information) in the system
> > >
> > > Regards,
> > >
> > > Kevin
> > >
> > >
> > >
> >
> >