Table design

[BillT]

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

 

[tina]

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

 

[John Nurick]

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

 

[BillT]

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

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

 

[BillT]

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

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

 

[rpw]

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.ht
(See the last download titled "Understandin
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

rp


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

Thank
Bil

-----Original Message----
Hi Bill

tblTruck

TruckID [primary key
TruckType [Clamp or Reach
other unchanging stuff about the truc

Is any Clamp truck allowed to use any Clamp battery, or

is each truc
only allowed to use its own two batteries? If each truck has its own tw
batteries, tblBatteries should look like this
tblBatterie
BatteryID 'primary ke
TruckID 'foreign key into tblTruck
DateAcquired and other stuf

If trucks share batteries, it will be more lik

tblBatterie
BatteryI
TruckTyp
DateAcquired and other stuf

Then I'd have a table to track the usage of each battery,

something lik
this
tblBatteryCycle
BatteryI
DateRemoved 'date battery removed from truc
RemovedFrom 'TruckID of truck the battery was removed fro
HoursUsage 'hours used on truc
DateCharged 'date battery came off charg
DateInstalled 'date battery installed in truc
InstalledIn 'TruckI
'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
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 fac
is recorded in the same BatteryCycle record. And when the battery i
installed in a truck that to needs to be recorded

If batteries are shared, the new battery can be found by

a query tha
selects from tblBatteryCycles
-the oldest recor
-where the truck type (looked up via BatteryID in tblBatteries
is the right on
-and DateCharged is not Null (i.e. the battery is charged
-and InstalledIn is Null (i.e. the battery is not alread
in another truck).

their batteries are used. (charged
battery's number, the truck# it is coming from and how
many hours the battery was used on the truck should
display what fresh battery is available for that
particular truck.
specific batteries. eg. Reach trucks uses only Reach
batteries and Clamp trucks only use Clamp batteries. 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.
tblTruckModel linked to tblBatteries
one to many Reach, Clamp identifies each different battery.
BillT
--

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

.

 

[John Nurick]

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.

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

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

 

[rpw]

----- 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 charge
.... and therefore no way of choosing which battery to use next. S
you're pretty much forced to use a "battery cycle" concept in som
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
tblTruckTyp
TtypeI
(Brand, other descriptive fields…

This table holds your truck inventory -have as many as you need
tblTruck
TruckI
TtypeI
(PurchDate, other descriptive fields…

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

This table holds your battery inventory - have as many as you need
tblBattInventor
BattI
BtypeI
(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
tblTruckBattUsag
UsageI
TruckI
BattI
EmpI
(DateInst, DateRemoved, HoursUsed, other descriptive fields…

This table records the type of charger - currently two brands but this allows for expansion
tblChargerTyp
ChgrTypeI
(Brand, other descriptive fields…

This table records your charger inventory
tblCharge
ChgrI
ChgrTypeI
(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
tblEmploye
EmpI
(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
tblBattHistor
HistI
UsageI
ChgrI
EmpI
(DateChgd, other descriptive fields…

TI

rpw

 

[John Nurick]

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.

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

 

[RPW]

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 on
battery being installed in one truck, where it may remain for zero o
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
query that joins tblTruckBattUsage and tblBattHistory in order to trac
a battery through all the Truck Battery Usage records in which i
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 b
putting them in brackets with the "other" fields. When you're storing
series of events, their datestamps often should be included in th
table's primary key; certainly they will need to be included in
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

rp


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

but there's nowhere to track when and whether a battery has been charge
.... and therefore no way of choosing which battery to use next. S
you're pretty much forced to use a "battery cycle" concept in som
fashion.

responded with a different layout (sans any explanation of the tables)
On my post I had expressed a lack of confidence in what informatio
could be queried out of the table records. If you don't mind, take
look at these tables and see if they will allow for queries tha
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]

Inline:

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.

We both understood Bill the same way: (a) A battery is removed from its
current truck before being charged. (b) Once it's charged it can be
installed in any suitable truck. (c) Batteries are only used in trucks.

If we're right, there's no need for independent tracking of "the history
of installing batteries in trucks" and "the history of charging and
discharging batteries", because you can't charge a battery without
having removed it from a truck, and you can't use a battery without
installing it in a 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.

Substitute "wrong" if you prefer<g>. But I was just referring to the
idea of a table that recorded events that happen to individual batteries
without storing the BatteryID.

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

As I said above, we seem to agree on the scenario; and if we've got that
right I don't see the need for tracking charging events independently of
discharging events. Even if (which seems not to be the case) a battery
may be installed in one truck, used a bit, and then installed in another
without being charged, the structure I proposed can handle it (perhaps
with the addition of one more field tblBatteryCycles).

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?

Just that.

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.

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

 

[rpw]

John, thanks for your explainations - I learned a little more and it all helps

rp

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

Inline

On Mon, 24 May 2004 19:36:04 -0700, RP

Hi John
battery being installed in one truck, where it may remain for zero o
more charge/discharge cycles?

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

We both understood Bill the same way: (a) A battery is removed from it
current truck before being charged. (b) Once it's charged it can b
installed in any suitable truck. (c) Batteries are only used in trucks

If we're right, there's no need for independent tracking of "the histor
of installing batteries in trucks" and "the history of charging an
discharging batteries", because you can't charge a battery withou
having removed it from a truck, and you can't use a battery withou
installing it in a 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
query that joins tblTruckBattUsage and tblBattHistory in order to trac
a battery through all the Truck Battery Usage records in which i
appears. This seems perverse.

Substitute "wrong" if you prefer<g>. But I was just referring to th
idea of a table that recorded events that happen to individual batterie
without storing the BatteryID.

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 proble
differently than you - Usage and Charging History as separate topics.
interpreted "fresh batteries" as "re-charged batteries" rather than "ne
batteries". I interpreted dis-charged (or "dead") batteries as remove
from the truck and placed onto a charger. Under that scenario, if yo
wanted to know which batteries are available for putting into anothe
truck, you'd need to know if each particular battery was already on
truck, freshly charged, or charging on a charger, so wouldn't you stil
have to search both the usage records and the charging records? Or i
that the kind of thinking you meant was 'perverse'

As I said above, we seem to agree on the scenario; and if we've got tha
right I don't see the need for tracking charging events independently o
discharging events. Even if (which seems not to be the case) a batter
may be installed in one truck, used a bit, and then installed in anothe
without being charged, the structure I proposed can handle it (perhap
with the addition of one more field tblBatteryCycles).

Also, you're underplaying the importance of fields such as DateInst b
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.
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?as part of the PK prevent inadvertant duplication of records?

Just that.

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.

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.