Table design

Hi:

Here's the scenario: (first database)

I'm trying to create a database for Forklifts and how
their batteries are used. (charged)

1. The program should prompt the user to input the dead
battery's number, the truck# it is coming from and how
many hours the battery was used on the truck.

2. After inputting the dead battery#, the program should
display what fresh battery is available for that
particular truck.

There are two different types of trucks that use only
specific batteries. eg. Reach trucks uses only Reach
batteries and Clamp trucks only use Clamp batteries.

My problem: There are two batteries for each truck. I am
unable to link my battery table to the truck table because
there are twice as many records in the battery table .
Truck#'s are unique: 14, 15, 16 etc.
Battery#'s are also unique: 14a, 14b, 15a, 15b etc.
Charger#'s are also unique: 14,15,16 etc.

My tables:
tblTruckModel linked to tblBatteries
one to many Reach, Clamp identifies each different battery.

tblLiftTruck unlinked
Truck#

tlbBatteries unlinked

tblEmployees unlinked

TIA
BillT

suggest the following table setup

tblBatteryTypes
BatTypeID (primary key)
BatTypeName
(2 records at this time: Clamp, and Reach)

tblTrucks
TruckID (primary key)
BatTypeID (foreign key from tblBatteryTypes)

tblBatteries
BatID (primary key)
BatTypeID (foreign key from tblBatteryTypes)
BatCharged (Yes/No field)
BatAvailable (Yes/No field)

suggest you track what batteries are put into each truck, rather than just
taken out, so you can also track "available" batteries.

tblTruckBatterySwaps
SwapID (primary key, autonumber data type)
TruckID (foreign key from tblTrucks)
BatID (foreign key from tblBatteries)
InDate
OutDate
HoursUsed

because each truck is assigned a battery type, and each battery is also
assigned a battery type (in their respective tables), and because
tblBatteries has Charged - Yes/No and Available - Yes/No fields, you can do
the following:

1. write code to automatically change a battery's Charged field to No when
the battery is removed from a truck.
2. allow data entry to change a battery's Charged field to Yes when it's
recharged, and write code to automatically change the Available field to
Yes.
3. when entering a new record for a battery being put into a truck, use a
combo box (drop down) to list only batteries of the correct type, that are
charged and available.
4. write code to automatically change a battery's Available field to No when
the battery is selected in a new "put battery in truck" record.

hth


"BillT" wrote in message
...
Hi:

Here's the scenario: (first database)

I'm trying to create a database for Forklifts and how
their batteries are used. (charged)

1. The program should prompt the user to input the dead
battery's number, the truck# it is coming from and how
many hours the battery was used on the truck.

2. After inputting the dead battery#, the program should
display what fresh battery is available for that
particular truck.

There are two different types of trucks that use only
specific batteries. eg. Reach trucks uses only Reach
batteries and Clamp trucks only use Clamp batteries.

My problem: There are two batteries for each truck. I am
unable to link my battery table to the truck table because
there are twice as many records in the battery table .
Truck#'s are unique: 14, 15, 16 etc.
Battery#'s are also unique: 14a, 14b, 15a, 15b etc.
Charger#'s are also unique: 14,15,16 etc.

My tables:
tblTruckModel linked to tblBatteries
one to many Reach, Clamp identifies each different battery.

tblLiftTruck unlinked
Truck#

tlbBatteries unlinked

tblEmployees unlinked

TIA
BillT

Hi Bill,

tblTrucks
TruckID [primary key]
TruckType [Clamp or Reach]
other unchanging stuff about the truck

Is any Clamp truck allowed to use any Clamp battery, or is each truck
only allowed to use its own two batteries? If each truck has its own two
batteries, tblBatteries should look like this:
tblBatteries
BatteryID 'primary key
TruckID 'foreign key into tblTrucks
DateAcquired and other stuff

If trucks share batteries, it will be more like
tblBatteries
BatteryID
TruckType
DateAcquired and other stuff

Then I'd have a table to track the usage of each battery, something like
this:
tblBatteryCycles
BatteryID
DateRemoved 'date battery removed from truck
RemovedFrom 'TruckID of truck the battery was removed from
HoursUsage 'hours used on truck
DateCharged 'date battery came off charge
DateInstalled 'date battery installed in truck
InstalledIn 'TruckID
'other fields, e.g. to track who filled in the form,
whether electrolyte levels and SG were checked, etc.
Obviously the RemovedFrom and InstalledIn fields are not needed if a
given battery is only ever installed in one truck.

So each time a battery is removed, a BatteryCycle record is created.
Each time a battery comes off charge (has been fully charged), the fact
is recorded in the same BatteryCycle record. And when the battery is
installed in a truck that to needs to be recorded.

If batteries are shared, the new battery can be found by a query that
selects from tblBatteryCycles
-the oldest record
-where the truck type (looked up via BatteryID in tblBatteries)
is the right one
-and DateCharged is not Null (i.e. the battery is charged)
-and InstalledIn is Null (i.e. the battery is not already
in another truck).




On Thu, 20 May 2004 08:54:19 -0700, "BillT"
wrote:

Hi:

Here's the scenario: (first database)

I'm trying to create a database for Forklifts and how
their batteries are used. (charged)

1. The program should prompt the user to input the dead
battery's number, the truck# it is coming from and how
many hours the battery was used on the truck.

2. After inputting the dead battery#, the program should
display what fresh battery is available for that
particular truck.

There are two different types of trucks that use only
specific batteries. eg. Reach trucks uses only Reach
batteries and Clamp trucks only use Clamp batteries.

My problem: There are two batteries for each truck. I am
unable to link my battery table to the truck table because
there are twice as many records in the battery table .
Truck#'s are unique: 14, 15, 16 etc.
Battery#'s are also unique: 14a, 14b, 15a, 15b etc.
Charger#'s are also unique: 14,15,16 etc.

My tables:
tblTruckModel linked to tblBatteries
one to many Reach, Clamp identifies each different battery.

tblLiftTruck unlinked
Truck#

tlbBatteries unlinked

tblEmployees unlinked

TIA
BillT

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Thanks people for your most generous time and expert
advise.

BillT
-----Original Message-----
Hi Bill,

tblTrucks
TruckID [primary key]
TruckType [Clamp or Reach]
other unchanging stuff about the truck

Is any Clamp truck allowed to use any Clamp battery, or
is each truck
only allowed to use its own two batteries? If each truck
has its own two
batteries, tblBatteries should look like this:
tblBatteries
BatteryID 'primary key
TruckID 'foreign key into tblTrucks
DateAcquired and other stuff

If trucks share batteries, it will be more like
tblBatteries
BatteryID
TruckType
DateAcquired and other stuff

Then I'd have a table to track the usage of each battery,
something like
this:
tblBatteryCycles
BatteryID
DateRemoved 'date battery removed from truck
RemovedFrom 'TruckID of truck the battery was
removed from
HoursUsage 'hours used on truck
DateCharged 'date battery came off charge
DateInstalled 'date battery installed in truck
InstalledIn 'TruckID
'other fields, e.g. to track who filled in the
form,
whether electrolyte levels and SG were
checked, etc.
Obviously the RemovedFrom and InstalledIn fields are not
needed if a
given battery is only ever installed in one truck.

So each time a battery is removed, a BatteryCycle record
is created.
Each time a battery comes off charge (has been fully
charged), the fact
is recorded in the same BatteryCycle record. And when the
battery is
installed in a truck that to needs to be recorded.

If batteries are shared, the new battery can be found by
a query that
selects from tblBatteryCycles
-the oldest record
-where the truck type (looked up via BatteryID
in tblBatteries)
is the right one
-and DateCharged is not Null (i.e. the battery
is charged)
-and InstalledIn is Null (i.e. the battery
is not already
in another truck).




On Thu, 20 May 2004 08:54:19 -0700, "BillT"
wrote:

Hi:

Here's the scenario: (first database)

I'm trying to create a database for Forklifts and how
their batteries are used. (charged)

1. The program should prompt the user to input the dead
battery's number, the truck# it is coming from and how
many hours the battery was used on the truck.

2. After inputting the dead battery#, the program
should
display what fresh battery is available for that
particular truck.

There are two different types of trucks that use only
specific batteries. eg. Reach trucks uses only Reach
batteries and Clamp trucks only use Clamp batteries.

My problem: There are two batteries for each truck. I
am
unable to link my battery table to the truck table
because
there are twice as many records in the battery table .
Truck#'s are unique: 14, 15, 16 etc.
Battery#'s are also unique: 14a, 14b, 15a, 15b etc.
Charger#'s are also unique: 14,15,16 etc.

My tables:
tblTruckModel linked to tblBatteries
one to many Reach, Clamp identifies each different
battery.

tblLiftTruck unlinked
Truck#

tlbBatteries unlinked

tblEmployees unlinked

TIA
BillT

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Hi John:
Clamp batteries can be used on any Clamp Trucks and the
same for the Reach batteries and trucks.
Looking at your 2nd tblBatteries table, I'm not sure how
to join the two tables. Keep in mind, that there are 31
batteries and only 15 trucks.

Thanks
Bill
-----Original Message-----
Hi Bill,

tblTrucks
TruckID [primary key]
TruckType [Clamp or Reach]
other unchanging stuff about the truck

Is any Clamp truck allowed to use any Clamp battery, or
is each truck
only allowed to use its own two batteries? If each truck
has its own two
batteries, tblBatteries should look like this:
tblBatteries
BatteryID 'primary key
TruckID 'foreign key into tblTrucks
DateAcquired and other stuff

If trucks share batteries, it will be more like
tblBatteries
BatteryID
TruckType
DateAcquired and other stuff

Then I'd have a table to track the usage of each battery,
something like
this:
tblBatteryCycles
BatteryID
DateRemoved 'date battery removed from truck
RemovedFrom 'TruckID of truck the battery was
removed from
HoursUsage 'hours used on truck
DateCharged 'date battery came off charge
DateInstalled 'date battery installed in truck
InstalledIn 'TruckID
'other fields, e.g. to track who filled in the
form,
whether electrolyte levels and SG were
checked, etc.
Obviously the RemovedFrom and InstalledIn fields are not
needed if a
given battery is only ever installed in one truck.

So each time a battery is removed, a BatteryCycle record
is created.
Each time a battery comes off charge (has been fully
charged), the fact
is recorded in the same BatteryCycle record. And when the
battery is
installed in a truck that to needs to be recorded.

If batteries are shared, the new battery can be found by
a query that
selects from tblBatteryCycles
-the oldest record
-where the truck type (looked up via BatteryID
in tblBatteries)
is the right one
-and DateCharged is not Null (i.e. the battery
is charged)
-and InstalledIn is Null (i.e. the battery
is not already
in another truck).




On Thu, 20 May 2004 08:54:19 -0700, "BillT"
wrote:

Hi:

Here's the scenario: (first database)

I'm trying to create a database for Forklifts and how
their batteries are used. (charged)

1. The program should prompt the user to input the dead
battery's number, the truck# it is coming from and how
many hours the battery was used on the truck.

2. After inputting the dead battery#, the program
should
display what fresh battery is available for that
particular truck.

There are two different types of trucks that use only
specific batteries. eg. Reach trucks uses only Reach
batteries and Clamp trucks only use Clamp batteries.

My problem: There are two batteries for each truck. I
am
unable to link my battery table to the truck table
because
there are twice as many records in the battery table .
Truck#'s are unique: 14, 15, 16 etc.
Battery#'s are also unique: 14a, 14b, 15a, 15b etc.
Charger#'s are also unique: 14,15,16 etc.

My tables:
tblTruckModel linked to tblBatteries
one to many Reach, Clamp identifies each different
battery.

tblLiftTruck unlinked
Truck#

tlbBatteries unlinked

tblEmployees unlinked

TIA
BillT

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Hi BillT,

I's like to add my thoughts about what John proposed and maybe help you move along on your project.

It appears John's design intended that tblTruck and tblBatteries do not ever have a 'direct' link.

In tblBatteryCycles, the batteryId and truckID are both there. This is the junction table and this is where the two tables are linked. This is typical when there is a potential many-to-many relationship - as in One truck may use Many different batteries and One battery may be used in Many different trucks (over time).

By the way, if this is your first database endeavor, I suggest that you check out this link on Normalization. I got it from Jeff Conrad on a previous post.

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding
Normalization")

Plus, I'm sure if you search this newsgroup for his name, you'll find that he sometimes posts a very long list of links that have tons of useful information.

Also, if you want any 'explanation' of the table design that I posted in your other thread, post back there and I'll be glad to respond.

rpw


----- BillT wrote: -----

Hi John:
Clamp batteries can be used on any Clamp Trucks and the
same for the Reach batteries and trucks.
Looking at your 2nd tblBatteries table, I'm not sure how
to join the two tables. Keep in mind, that there are 31
batteries and only 15 trucks.

Thanks
Bill
-----Original Message-----
Hi Bill,
tblTrucks
TruckID [primary key]
TruckType [Clamp or Reach]
other unchanging stuff about the truck
Is any Clamp truck allowed to use any Clamp battery, or
is each truck
only allowed to use its own two batteries? If each truck
has its own two
batteries, tblBatteries should look like this:
tblBatteries
BatteryID 'primary key
TruckID 'foreign key into tblTrucks
DateAcquired and other stuff
If trucks share batteries, it will be more like
tblBatteries
BatteryID
TruckType
DateAcquired and other stuff
Then I'd have a table to track the usage of each battery,
something like
this:
tblBatteryCycles
BatteryID
DateRemoved 'date battery removed from truck
RemovedFrom 'TruckID of truck the battery was
removed from
HoursUsage 'hours used on truck
DateCharged 'date battery came off charge
DateInstalled 'date battery installed in truck
InstalledIn 'TruckID
'other fields, e.g. to track who filled in the
form,
whether electrolyte levels and SG were
checked, etc.
Obviously the RemovedFrom and InstalledIn fields are not
needed if a
given battery is only ever installed in one truck.
So each time a battery is removed, a BatteryCycle record
is created.
Each time a battery comes off charge (has been fully
charged), the fact
is recorded in the same BatteryCycle record. And when the
battery is
installed in a truck that to needs to be recorded.
If batteries are shared, the new battery can be found by
a query that
selects from tblBatteryCycles
-the oldest record
-where the truck type (looked up via BatteryID
in tblBatteries)
is the right one
-and DateCharged is not Null (i.e. the battery
is charged)
-and InstalledIn is Null (i.e. the battery
is not already
in another truck).
On Thu, 20 May 2004 08:54:19 -0700, "BillT"
wrote:
Hi:
Here's the scenario: (first database)
I'm trying to create a database for Forklifts and how
their batteries are used. (charged)
1. The program should prompt the user to input the dead
battery's number, the truck# it is coming from and how
many hours the battery was used on the truck.
2. After inputting the dead battery#, the program
should
display what fresh battery is available for that
particular truck.
There are two different types of trucks that use only
specific batteries. eg. Reach trucks uses only Reach
batteries and Clamp trucks only use Clamp batteries.
My problem: There are two batteries for each truck. I
am
unable to link my battery table to the truck table
because
there are twice as many records in the battery table .
Truck#'s are unique: 14, 15, 16 etc.
Battery#'s are also unique: 14a, 14b, 15a, 15b etc.
Charger#'s are also unique: 14,15,16 etc.
My tables:
tblTruckModel linked to tblBatteries
one to many Reach, Clamp identifies each different
battery.
tblLiftTruck unlinked
Truck#
tlbBatteries unlinked
tblEmployees unlinked
TIA
BillT
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.

As RPW said, there's a many-to-many relationship between batteries and
trucks - any truck can use any suitable battery. This is captured by the
"Battery Cycle" entity: tblBatteryCycles is related to both tblBatteries
(via BatteryID) and tblTrucks (via TruckID).

I'm sure that this is the correct basic structure. The detail of the
"Battery Cycle" entity can probably be improved, but it seemed to me to
be the simplest way of doing it.

For instance, I suggested the _removal_ of a discharged battery from a
truck as the starting point, because at that point all the key data for
the BatteryCycle is available, i.e. BatteryID, TruckID and HoursUsage.
So you can create the record at that point, and later just record the
fact that the battery is charged and ready for use. If the starting
point was the _installation_ of a charged battery, you'd have to visit
the BatteryCycle record three times: once to create it (with BatteryID
and TruckID and a datestamp); once, on removal, to enter HoursUsage; and
the third time to record that the battery is charged and ready.

If you like you could focus on Trucks rather than Batteries. You'd still
need the three tables, but instead of tblBatteryCycles you could have
tblTrucksBatteries
TruckID
BatteryID
DateInstalled
DateRemoved
HoursUsage

You could query this table to get the usage history of each battery -
but there's nowhere to track when and whether a battery has been charged
.... and therefore no way of choosing which battery to use next. So
you're pretty much forced to use a "battery cycle" concept in some
fashion.

On Fri, 21 May 2004 10:22:54 -0700, "BillT"
wrote:

Hi John:
Clamp batteries can be used on any Clamp Trucks and the
same for the Reach batteries and trucks.
Looking at your 2nd tblBatteries table, I'm not sure how
to join the two tables. Keep in mind, that there are 31
batteries and only 15 trucks.

Thanks
Bill
-----Original Message-----
Hi Bill,

tblTrucks
TruckID [primary key]
TruckType [Clamp or Reach]
other unchanging stuff about the truck

Is any Clamp truck allowed to use any Clamp battery, or
is each truck
only allowed to use its own two batteries? If each truck
has its own two
batteries, tblBatteries should look like this:
tblBatteries
BatteryID 'primary key
TruckID 'foreign key into tblTrucks
DateAcquired and other stuff

If trucks share batteries, it will be more like
tblBatteries
BatteryID
TruckType
DateAcquired and other stuff

Then I'd have a table to track the usage of each battery,
something like
this:
tblBatteryCycles
BatteryID
DateRemoved 'date battery removed from truck
RemovedFrom 'TruckID of truck the battery was
removed from
HoursUsage 'hours used on truck
DateCharged 'date battery came off charge
DateInstalled 'date battery installed in truck
InstalledIn 'TruckID
'other fields, e.g. to track who filled in the
form,
whether electrolyte levels and SG were
checked, etc.
Obviously the RemovedFrom and InstalledIn fields are not
needed if a
given battery is only ever installed in one truck.

So each time a battery is removed, a BatteryCycle record
is created.
Each time a battery comes off charge (has been fully
charged), the fact
is recorded in the same BatteryCycle record. And when the
battery is
installed in a truck that to needs to be recorded.

If batteries are shared, the new battery can be found by
a query that
selects from tblBatteryCycles
-the oldest record
-where the truck type (looked up via BatteryID
in tblBatteries)
is the right one
-and DateCharged is not Null (i.e. the battery
is charged)
-and InstalledIn is Null (i.e. the battery
is not already
in another truck).




On Thu, 20 May 2004 08:54:19 -0700, "BillT"
wrote:

Hi:

Here's the scenario: (first database)

I'm trying to create a database for Forklifts and how
their batteries are used. (charged)

1. The program should prompt the user to input the dead
battery's number, the truck# it is coming from and how
many hours the battery was used on the truck.

2. After inputting the dead battery#, the program
should
display what fresh battery is available for that
particular truck.

There are two different types of trucks that use only
specific batteries. eg. Reach trucks uses only Reach
batteries and Clamp trucks only use Clamp batteries.

My problem: There are two batteries for each truck. I
am
unable to link my battery table to the truck table
because
there are twice as many records in the battery table .
Truck#'s are unique: 14, 15, 16 etc.
Battery#'s are also unique: 14a, 14b, 15a, 15b etc.
Charger#'s are also unique: 14,15,16 etc.

My tables:
tblTruckModel linked to tblBatteries
one to many Reach, Clamp identifies each different
battery.

tblLiftTruck unlinked
Truck#

tlbBatteries unlinked

tblEmployees unlinked

TIA
BillT

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

----- John Nurick wrote: -----

snip
You could query this table to get the usage history of each battery -
but there's nowhere to track when and whether a battery has been charged
.... and therefore no way of choosing which battery to use next. So
you're pretty much forced to use a "battery cycle" concept in some
fashion.

Hi John,

BillT had posted the same question in the table design group and I had responded with a different layout (sans any explanation of the tables). On my post I had expressed a lack of confidence in what information could be queried out of the table records. If you don't mind, take a look at these tables and see if they will allow for queries that determine which batteries are charged, which are in use and in which truck, and who did the install and re-charge work for each battery. (I'm trying to strengthen my table design skills and have not worked much on my query skills yet.)

There are currently only two types of trucks, but this table allows for adding more types.
tblTruckType
TtypeID
(Brand, other descriptive fields…)

This table holds your truck inventory -have as many as you need.
tblTrucks
TruckID
TtypeID
(PurchDate, other descriptive fields…)

This table holds the types of batteries - again, currently only two but could be increased for more.
tblBattType
BtypeID
(Brand, other descriptive fields…)

This table holds your battery inventory - have as many as you need.
tblBattInventory
BattID
BtypeID
(DateRec, DateDestroyed, other descriptive fields…)

This table is the junction table between trucks and batteries. You record what battery went into which truck, when it went in, and when it came out. You can assign as many batteries as you want (two?) to a particular truck by each battery installation having its own usageID.
tblTruckBattUsage
UsageID
TruckID
BattID
EmpID
(DateInst, DateRemoved, HoursUsed, other descriptive fields…)

This table records the type of charger - currently two brands but this allows for expansion.
tblChargerType
ChgrTypeID
(Brand, other descriptive fields…)

This table records your charger inventory.
tblCharger
ChgrID
ChgrTypeID
(Brand, DatePurch, other descriptive fields…)

This table records the employees - it is available here so you can track who worked on the installation or charging of the batteries.
tblEmployee
EmpID
(FirstName, LastName, Position [could be another table?], other descriptive fields…)

This table records the re-charging of each battery. The usageID is in this table because I figured that each battery would/should be charged upon removal from a truck.
tblBattHistory
HistID
UsageID
ChgrID
EmpID
(DateChgd, other descriptive fields…)

TIA

rpw

A couple of comments:

First, I don't understand your entity "Truck Battery Usage". Is it one
battery being installed in one truck, where it may remain for zero or
more charge/discharge cycles?

Next, why does your Battery History table not include the BatteryID?
This means that to get the history of a battery you have to write a
query that joins tblTruckBattUsage and tblBattHistory in order to trace
a battery through all the Truck Battery Usage records in which it
appears. This seems perverse.

Also, you're underplaying the importance of fields such as DateInst by
putting them in brackets with the "other" fields. When you're storing a
series of events, their datestamps often should be included in the
table's primary key; certainly they will need to be included in a
multi-field unique index.


On Mon, 24 May 2004 09:06:02 -0700, rpw
wrote:



----- John Nurick wrote: -----

snip
You could query this table to get the usage history of each battery -
but there's nowhere to track when and whether a battery has been charged
.... and therefore no way of choosing which battery to use next. So
you're pretty much forced to use a "battery cycle" concept in some
fashion.

Hi John,

BillT had posted the same question in the table design group and I had
responded with a different layout (sans any explanation of the tables).
On my post I had expressed a lack of confidence in what information
could be queried out of the table records. If you don't mind, take a
look at these tables and see if they will allow for queries that
determine which batteries are charged, which are in use and in which
truck, and who did the install and re-charge work for each battery.
(I'm trying to strengthen my table design skills and have not worked
much on my query skills yet.)

There are currently only two types of trucks, but this table allows for adding more types.
tblTruckType
TtypeID
(Brand, other descriptive fields…)

This table holds your truck inventory -have as many as you need.
tblTrucks
TruckID
TtypeID
(PurchDate, other descriptive fields…)

This table holds the types of batteries - again, currently only two but could be increased for more.
tblBattType
BtypeID
(Brand, other descriptive fields…)

This table holds your battery inventory - have as many as you need.
tblBattInventory
BattID
BtypeID
(DateRec, DateDestroyed, other descriptive fields…)

This table is the junction table between trucks and batteries. You record what battery went into which truck, when it went in, and when it came out. You can assign as many batteries as you want (two?) to a particular truck by each battery installation having its own usageID.
tblTruckBattUsage
UsageID
TruckID
BattID
EmpID
(DateInst, DateRemoved, HoursUsed, other descriptive fields…)

This table records the type of charger - currently two brands but this allows for expansion.
tblChargerType
ChgrTypeID
(Brand, other descriptive fields…)

This table records your charger inventory.
tblCharger
ChgrID
ChgrTypeID
(Brand, DatePurch, other descriptive fields…)

This table records the employees - it is available here so you can track who worked on the installation or charging of the batteries.
tblEmployee
EmpID
(FirstName, LastName, Position [could be another table?], other descriptive fields…)

This table records the re-charging of each battery. The usageID is in this table because I figured that each battery would/should be charged upon removal from a truck.
tblBattHistory
HistID
UsageID
ChgrID
EmpID
(DateChgd, other descriptive fields…)

TIA

rpw

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Hi John,

Thank you for taking the time to respond.


----- John Nurick wrote: -----

A couple of comments:

First, I don't understand your entity "Truck Battery Usage". Is it one
battery being installed in one truck, where it may remain for zero or
more charge/discharge cycles?

To answer both questions - Yes, it is one battery in one truck and No, it does not remain through charging cycles. I was thinking that placing batteries into trucks was a separate topic from charging the battery. One battery might be installed in one truck where it remains until it is removed. Any given battery might be removed from one truck and placed into another, or it might be removed and placed onto the charger (as opposed to remaining in the truck during the re-charging period which would make the truck unavailable). Plus, I figured that because BillT mentioned that dead batteries were coming off the trucks that the charging did not take place on the truck. After the battery is re-charged it might then be installed into another, different truck.

Next, why does your Battery History table not include the BatteryID?
This means that to get the history of a battery you have to write a
query that joins tblTruckBattUsage and tblBattHistory in order to trace
a battery through all the Truck Battery Usage records in which it
appears. This seems perverse.

"Perverse" is an interesting term. Although I don't know if you mean the mis-guided table design or the query of both usage and history tables. In either case, I'm glad that I asked - you're right, it should be a BatteryID (and not a UsageID). As far as querying both table records, I guess I read the problem differently than you - Usage and Charging History as separate topics. I interpreted "fresh batteries" as "re-charged batteries" rather than "new batteries". I interpreted dis-charged (or "dead") batteries as removed from the truck and placed onto a charger. Under that scenario, if you wanted to know which batteries are available for putting into another truck, you'd need to know if each particular battery was already on a truck, freshly charged, or charging on a charger, so wouldn't you still have to search both the usage records and the charging records? Or is that the kind of thinking you meant was 'perverse'?

Also, you're underplaying the importance of fields such as DateInst by
putting them in brackets with the "other" fields. When you're storing a
series of events, their datestamps often should be included in the
table's primary key; certainly they will need to be included in a
multi-field unique index.

Obviously, I added DateInst as a field because I thought it was an important topic. However, I don't understand 'why' it should be a part of the PK or unique index. I suspect that an explanation would be lengthy. If you don't have the time to explain it, is it possible that you could direct me to a link, a chapter in a book, or article that explains it?

Hmmm, as I'm re-reading this before hitting 'Send', does having the date field as part of the PK prevent inadvertant duplication of records?

Thanks again for your time,

rpw


On Mon, 24 May 2004 09:06:02 -0700, rpw
wrote:

----- John Nurick wrote: -----
snip You could query this table to get the usage history of each battery -
but there's nowhere to track when and whether a battery has been charged
.... and therefore no way of choosing which battery to use next. So
you're pretty much forced to use a "battery cycle" concept in some
fashion.
Hi John,
BillT had posted the same question in the table design group and I had
responded with a different layout (sans any explanation of the tables).
On my post I had expressed a lack of confidence in what information
could be queried out of the table records. If you don't mind, take a
look at these tables and see if they will allow for queries that
determine which batteries are charged, which are in use and in which
truck, and who did the install and re-charge work for each battery.
(I'm trying to strengthen my table design skills and have not worked
much on my query skills yet.)
There are currently only two types of trucks, but this table allows for adding more types.
tblTruckType
TtypeID
(Brand, other descriptive fields…)
This table holds your truck inventory -have as many as you need.
tblTrucks
TruckID
TtypeID
(PurchDate, other descriptive fields…)
This table holds the types of batteries - again, currently only two but could be increased for more.
tblBattType
BtypeID
(Brand, other descriptive fields…)
This table holds your battery inventory - have as many as you need.
tblBattInventory
BattID
BtypeID
(DateRec, DateDestroyed, other descriptive fields…)
This table is the junction table between trucks and batteries. You record what battery went into which truck, when it went in, and when it came out. You can assign as many batteries as you want (two?) to a particular truck by each battery installation having its own usageID.
tblTruckBattUsage
UsageID
TruckID
BattID
EmpID
(DateInst, DateRemoved, HoursUsed, other descriptive fields…)
This table records the type of charger - currently two brands but this allows for expansion.
tblChargerType
ChgrTypeID
(Brand, other descriptive fields…)
This table records your charger inventory.
tblCharger
ChgrID
ChgrTypeID
(Brand, DatePurch, other descriptive fields…)
This table records the employees - it is available here so you can track who worked on the installation or charging of the batteries.
tblEmployee
EmpID
(FirstName, LastName, Position [could be another table?], other descriptive fields…)
This table records the re-charging of each battery. The usageID is in this table because I figured that each battery would/should be charged upon removal from a truck.
tblBattHistory
HistID
UsageID
ChgrID
EmpID
(DateChgd, other descriptive fields…)
TIA
rpw

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.