Designing Table Structures

[Scott A]

I've got a table design issue that's stumping me - I'm
wondering if anyone has faced a similar problem and can
make a few recommendations as to how many tables will be
required to hold the data:

I've got one table with equipment records. This table
contains all of the details related to the piece (power
supply, name, type, etc.)...

Each piece of equipment is maintained according a schedule
(3 months, 6 months, 1 year), but can be maintained by
more than one person according to more than one schedule...

I need the database to record which types of maintenance
each piece is subject to, and the interval for which it is
maintained, and also track the dates when the services are
actually performed.

Any suggestions?

 

[Sandra Daigle]

Funny you should ask, I'm working on something very similar right now. I
would say that you need at least three other tables - MaintTypes,
EquipMaintSched, EquipMaintLog

MaintType would contain all types of maintenance available

MaintType
--------------------
MaintTypeId*
MaintType**

EquipSchedMaint would have one record for each type of required maintenance
for a particular piece of equipment and the maintenance interval for that
maintenance item.

EquipMaintSched
----------------------
EquipId*
MaintTypeId*
MaintInterval (*you would have to define the units for the interval days,
weeks, months, years)

EquipMaintLog would have one record for each time a maintenance item is
completed on a piece of equipment.

EquipMaintLog
-------------------------
EquipId*
MaintTypeID*
MaintDate*
VendorId
Remarks

* is for PK fields
** is for unique index fields

Note that I didn't columns for NextMaintDate - technically this should be a
calculated field in a query since it is dependant on the most recent entry
in the EquipMaintLog. Depending on your situation you might want to include
this as a field on EquipMaintSched - just keep in mind the potential
pitfalls of storing data that is dependant on other data. Actually, since
there may be times that you want to force a particular date for maintenance
you might have to have a field for it (hmmm . . . thinking about my own
project here).

Also, I would assume that there could also be a need for including Vendor
information regarding which Vendors do which types of maintenance, which are
approved for the maintenance items and which is actually used on a
particular date.

 

[Scott A]

Pinch me!

Thanks for the validation - that's exactly what I had from
the begining!

One wrinkle that's making me scratch my (puny) brain:

Depending on the maintenance type selected, the service
will be performed internally or externally. For external
service, I want to store the VendorID (FK), but for
internal service, I would like it to be the EmployeeID.
What is the best approach?

-----Original Message-----
Funny you should ask, I'm working on something very similar right now. I
would say that you need at least three other tables - MaintTypes,
EquipMaintSched, EquipMaintLog

MaintType would contain all types of maintenance available

MaintType
--------------------
MaintTypeId*
MaintType**

EquipSchedMaint would have one record for each type of required maintenance
for a particular piece of equipment and the maintenance interval for that
maintenance item.

EquipMaintSched
----------------------
EquipId*
MaintTypeId*
MaintInterval (*you would have to define the units for the interval days,
weeks, months, years)

EquipMaintLog would have one record for each time a maintenance item is
completed on a piece of equipment.

EquipMaintLog
-------------------------
EquipId*
MaintTypeID*
MaintDate*
VendorId
Remarks

* is for PK fields
** is for unique index fields

Note that I didn't columns for NextMaintDate - technically this should be a
calculated field in a query since it is dependant on the most recent entry
in the EquipMaintLog. Depending on your situation you might want to include
this as a field on EquipMaintSched - just keep in mind the potential
pitfalls of storing data that is dependant on other data. Actually, since
there may be times that you want to force a particular date for maintenance
you might have to have a field for it (hmmm . . . thinking about my own
project here).

Also, I would assume that there could also be a need for including Vendor
information regarding which Vendors do which types of maintenance, which are
approved for the maintenance items and which is actually used on a
particular date.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I've got a table design issue that's stumping me - I'm
wondering if anyone has faced a similar problem and can
make a few recommendations as to how many tables will be
required to hold the data:

I've got one table with equipment records. This table
contains all of the details related to the piece (power
supply, name, type, etc.)...

Each piece of equipment is maintained according a schedule
(3 months, 6 months, 1 year), but can be maintained by
more than one person according to more than one schedule...

I need the database to record which types of maintenance
each piece is subject to, and the interval for which it is
maintained, and also track the dates when the services are
actually performed.

Any suggestions?

.

 

[Sandra Daigle]

Good point - you probably need another field on your EquipMaintSched for
MaintProviderType (Internal or External). Then there are a couple of
different ways to handle storing either Vendor or EmployeeID - probably the
easiest would be to have two fields, VendorId and EmployeeID then throught
the application, require the appropriate one depending on whether you select
Internal or External maintenance. This will allow you to maintain
referential integrity on either field without requiring both to be entered
on a record.

Another approach is to use a single MaintProviderID field and let it contain
either the VendorID or the EmployeeID depending on the value in
MaintProviderType. The downside to this approach is that you don't get
referential integrity with Vendors or Employees. On the plus side, you can
require that there is a value in the MaintProviderId field.

There are also other ways to do this but they might be overkill.

I'd be interested in hearing opinions on this from others since this type of
issue has been a common one in my projects and I've handled it several
different ways, never feeling that there is one surefire absolute best way
to handle it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Pinch me!

Thanks for the validation - that's exactly what I had from
the begining!

One wrinkle that's making me scratch my (puny) brain:

Depending on the maintenance type selected, the service
will be performed internally or externally. For external
service, I want to store the VendorID (FK), but for
internal service, I would like it to be the EmployeeID.
What is the best approach?

-----Original Message-----
Funny you should ask, I'm working on something very similar right
now. I would say that you need at least three other tables -
MaintTypes, EquipMaintSched, EquipMaintLog

MaintType would contain all types of maintenance available

MaintType
--------------------
MaintTypeId*
MaintType**

EquipSchedMaint would have one record for each type of required
maintenance for a particular piece of equipment and the maintenance
interval for that maintenance item.

EquipMaintSched
----------------------
EquipId*
MaintTypeId*
MaintInterval (*you would have to define the units for the interval
days, weeks, months, years)

EquipMaintLog would have one record for each time a maintenance item
is completed on a piece of equipment.

EquipMaintLog
-------------------------
EquipId*
MaintTypeID*
MaintDate*
VendorId
Remarks

* is for PK fields
** is for unique index fields

Note that I didn't columns for NextMaintDate - technically this
should be a calculated field in a query since it is dependant on the
most recent entry in the EquipMaintLog. Depending on your situation
you might want to include this as a field on EquipMaintSched - just
keep in mind the potential pitfalls of storing data that is
dependant on other data. Actually, since there may be times that you
want to force a particular date for maintenance you might have to
have a field for it (hmmm . . . thinking about my own project here).

Also, I would assume that there could also be a need for including
Vendor information regarding which Vendors do which types of
maintenance, which are approved for the maintenance items and which
is actually used on a particular date.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I've got a table design issue that's stumping me - I'm
wondering if anyone has faced a similar problem and can
make a few recommendations as to how many tables will be
required to hold the data:

I've got one table with equipment records. This table
contains all of the details related to the piece (power
supply, name, type, etc.)...

Each piece of equipment is maintained according a schedule
(3 months, 6 months, 1 year), but can be maintained by
more than one person according to more than one schedule...

I need the database to record which types of maintenance
each piece is subject to, and the interval for which it is
maintained, and also track the dates when the services are
actually performed.

Any suggestions?

.

 

[Lynn Trapp]

Sandra,
Have you considered putting a record in the Vendor Table for all Employees
who might also be Maintence providers? Thus, they are not only an Employee,
but are also a vendor. That way you have the joy of the single
MaintProviderID field AND referential integrity.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Good point - you probably need another field on your EquipMaintSched for
MaintProviderType (Internal or External). Then there are a couple of
different ways to handle storing either Vendor or EmployeeID - probably the
easiest would be to have two fields, VendorId and EmployeeID then throught
the application, require the appropriate one depending on whether you select
Internal or External maintenance. This will allow you to maintain
referential integrity on either field without requiring both to be entered
on a record.

Another approach is to use a single MaintProviderID field and let it contain
either the VendorID or the EmployeeID depending on the value in
MaintProviderType. The downside to this approach is that you don't get
referential integrity with Vendors or Employees. On the plus side, you can
require that there is a value in the MaintProviderId field.

There are also other ways to do this but they might be overkill.

I'd be interested in hearing opinions on this from others since this type of
issue has been a common one in my projects and I've handled it several
different ways, never feeling that there is one surefire absolute best way
to handle it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Pinch me!

Thanks for the validation - that's exactly what I had from
the begining!

One wrinkle that's making me scratch my (puny) brain:

Depending on the maintenance type selected, the service
will be performed internally or externally. For external
service, I want to store the VendorID (FK), but for
internal service, I would like it to be the EmployeeID.
What is the best approach?

-----Original Message-----
Funny you should ask, I'm working on something very similar right
now. I would say that you need at least three other tables -
MaintTypes, EquipMaintSched, EquipMaintLog

MaintType would contain all types of maintenance available

MaintType
--------------------
MaintTypeId*
MaintType**

EquipSchedMaint would have one record for each type of required
maintenance for a particular piece of equipment and the maintenance
interval for that maintenance item.

EquipMaintSched
----------------------
EquipId*
MaintTypeId*
MaintInterval (*you would have to define the units for the interval
days, weeks, months, years)

EquipMaintLog would have one record for each time a maintenance item
is completed on a piece of equipment.

EquipMaintLog
-------------------------
EquipId*
MaintTypeID*
MaintDate*
VendorId
Remarks

* is for PK fields
** is for unique index fields

Note that I didn't columns for NextMaintDate - technically this
should be a calculated field in a query since it is dependant on the
most recent entry in the EquipMaintLog. Depending on your situation
you might want to include this as a field on EquipMaintSched - just
keep in mind the potential pitfalls of storing data that is
dependant on other data. Actually, since there may be times that you
want to force a particular date for maintenance you might have to
have a field for it (hmmm . . . thinking about my own project here).

Also, I would assume that there could also be a need for including
Vendor information regarding which Vendors do which types of
maintenance, which are approved for the maintenance items and which
is actually used on a particular date.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Scott A wrote:
I've got a table design issue that's stumping me - I'm
wondering if anyone has faced a similar problem and can
make a few recommendations as to how many tables will be
required to hold the data:

I've got one table with equipment records. This table
contains all of the details related to the piece (power
supply, name, type, etc.)...

Each piece of equipment is maintained according a schedule
(3 months, 6 months, 1 year), but can be maintained by
more than one person according to more than one schedule...

I need the database to record which types of maintenance
each piece is subject to, and the interval for which it is
maintained, and also track the dates when the services are
actually performed.

Any suggestions?

.

 

[Lynn Trapp]

Scott,
Let me elaborate on what I posted below to Sandra. You can add a field to
your Vendor Table, called VendorType, and add a record to that table for
each of your employees (you can limit to those who might do maintenance if
you want). The Vendor Type for a regular vendor could be "Vendor" and for an
Employee it could be "Employee". That way you can select the Vendor ID for
the appropriate person (internal or external) from the same Vendor Table.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Pinch me!

Thanks for the validation - that's exactly what I had from
the begining!

One wrinkle that's making me scratch my (puny) brain:

Depending on the maintenance type selected, the service
will be performed internally or externally. For external
service, I want to store the VendorID (FK), but for
internal service, I would like it to be the EmployeeID.
What is the best approach?

-----Original Message-----
Funny you should ask, I'm working on something very similar right now. I
would say that you need at least three other tables - MaintTypes,
EquipMaintSched, EquipMaintLog

MaintType would contain all types of maintenance available

MaintType
--------------------
MaintTypeId*
MaintType**

EquipSchedMaint would have one record for each type of required maintenance
for a particular piece of equipment and the maintenance interval for that
maintenance item.

EquipMaintSched
----------------------
EquipId*
MaintTypeId*
MaintInterval (*you would have to define the units for the interval days,
weeks, months, years)

EquipMaintLog would have one record for each time a maintenance item is
completed on a piece of equipment.

EquipMaintLog
-------------------------
EquipId*
MaintTypeID*
MaintDate*
VendorId
Remarks

* is for PK fields
** is for unique index fields

Note that I didn't columns for NextMaintDate - technically this should be a
calculated field in a query since it is dependant on the most recent entry
in the EquipMaintLog. Depending on your situation you might want to include
this as a field on EquipMaintSched - just keep in mind the potential
pitfalls of storing data that is dependant on other data. Actually, since
there may be times that you want to force a particular date for maintenance
you might have to have a field for it (hmmm . . . thinking about my own
project here).

Also, I would assume that there could also be a need for including Vendor
information regarding which Vendors do which types of maintenance, which are
approved for the maintenance items and which is actually used on a
particular date.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I've got a table design issue that's stumping me - I'm
wondering if anyone has faced a similar problem and can
make a few recommendations as to how many tables will be
required to hold the data:

I've got one table with equipment records. This table
contains all of the details related to the piece (power
supply, name, type, etc.)...

Each piece of equipment is maintained according a schedule
(3 months, 6 months, 1 year), but can be maintained by
more than one person according to more than one schedule...

I need the database to record which types of maintenance
each piece is subject to, and the interval for which it is
maintained, and also track the dates when the services are
actually performed.

Any suggestions?

.

 

[Lynn Trapp]

Oh, the joys of maintaining Vendors in ANY system. It has to be the biggest
nightmare ever created.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi Lynn,

I hadn't considered that approach - for this purpose it might be a good
solution but in the more general sense (the other similar issues that I
have) I don't like it since you end up with two records for each entity that
has the functional crossover and inevitably, information on one will get
updated without getting updated on the other (Normalization 101 I believe
;-)).

My worst case involves Vendors who are also Customers. In my case, the
problem is also due to project evolution. Originally we had Customers and
Vendors and there was never a time when Customer data would mix with Vendor
data - there was no overlap in the application. Then we started logging
inbound shipments and every shipment needed to be tagged with the Shipper -
well Customers are shippers and Vendors are also Shippers, how should I
represent this? Had I known up front that we'd need this, I would have
started with a Shipper's table (or some other noun representing outside
entitys with whom we do business), then I would have had a separate table
that allowed us to indicate the different business relationships we have
with that entity so that that entity could participate in a variety of
different sub-applications. Ahhhh . . . the beauty of hindsight

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Sandra,
Have you considered putting a record in the Vendor Table for all
Employees who might also be Maintence providers? Thus, they are not
only an Employee, but are also a vendor. That way you have the joy of
the single MaintProviderID field AND referential integrity.

Good point - you probably need another field on your EquipMaintSched
for MaintProviderType (Internal or External). Then there are a
couple of different ways to handle storing either Vendor or
EmployeeID - probably the easiest would be to have two fields,
VendorId and EmployeeID then throught the application, require the
appropriate one depending on whether you select Internal or External
maintenance. This will allow you to maintain referential integrity
on either field without requiring both to be entered on a record.

Another approach is to use a single MaintProviderID field and let it
contain either the VendorID or the EmployeeID depending on the value
in MaintProviderType. The downside to this approach is that you
don't get referential integrity with Vendors or Employees. On the
plus side, you can require that there is a value in the
MaintProviderId field.

There are also other ways to do this but they might be overkill.

I'd be interested in hearing opinions on this from others since this
type of issue has been a common one in my projects and I've handled
it several different ways, never feeling that there is one surefire
absolute best way to handle it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott A wrote:
Pinch me!

Thanks for the validation - that's exactly what I had from
the begining!

One wrinkle that's making me scratch my (puny) brain:

Depending on the maintenance type selected, the service
will be performed internally or externally. For external
service, I want to store the VendorID (FK), but for
internal service, I would like it to be the EmployeeID.
What is the best approach?
-----Original Message-----
Funny you should ask, I'm working on something very similar right
now. I would say that you need at least three other tables -
MaintTypes, EquipMaintSched, EquipMaintLog

MaintType would contain all types of maintenance available

MaintType
--------------------
MaintTypeId*
MaintType**

EquipSchedMaint would have one record for each type of required
maintenance for a particular piece of equipment and the maintenance
interval for that maintenance item.

EquipMaintSched
----------------------
EquipId*
MaintTypeId*
MaintInterval (*you would have to define the units for the interval
days, weeks, months, years)

EquipMaintLog would have one record for each time a maintenance
item is completed on a piece of equipment.

EquipMaintLog
-------------------------
EquipId*
MaintTypeID*
MaintDate*
VendorId
Remarks

* is for PK fields
** is for unique index fields

Note that I didn't columns for NextMaintDate - technically this
should be a calculated field in a query since it is dependant on
the most recent entry in the EquipMaintLog. Depending on your
situation you might want to include this as a field on
EquipMaintSched - just keep in mind the potential pitfalls of
storing data that is dependant on other data. Actually, since
there may be times that you want to force a particular date for
maintenance you might have to have a field for it (hmmm . . .
thinking about my own project here).

Also, I would assume that there could also be a need for including
Vendor information regarding which Vendors do which types of
maintenance, which are approved for the maintenance items and which
is actually used on a particular date.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.

Scott A wrote:
I've got a table design issue that's stumping me - I'm
wondering if anyone has faced a similar problem and can
make a few recommendations as to how many tables will be
required to hold the data:

I've got one table with equipment records. This table
contains all of the details related to the piece (power
supply, name, type, etc.)...

Each piece of equipment is maintained according a schedule
(3 months, 6 months, 1 year), but can be maintained by
more than one person according to more than one schedule...

I need the database to record which types of maintenance
each piece is subject to, and the interval for which it is
maintained, and also track the dates when the services are
actually performed.

Any suggestions?

.

 

[Sandra Daigle]

Customers are the same way!!

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Oh, the joys of maintaining Vendors in ANY system. It has to be the
biggest nightmare ever created.

Hi Lynn,

I hadn't considered that approach - for this purpose it might be a good
solution but in the more general sense (the other similar issues that I
have) I don't like it since you end up with two records for each entity
that has the functional crossover and inevitably, information on one
will get updated without getting updated on the other (Normalization 101
I believe ;-)).

My worst case involves Vendors who are also Customers. In my case, the
problem is also due to project evolution. Originally we had Customers and
Vendors and there was never a time when Customer data would mix with
Vendor data - there was no overlap in the application. Then we started
logging inbound shipments and every shipment needed to be tagged with the Shipper -
well Customers are shippers and Vendors are also Shippers, how should I
represent this? Had I known up front that we'd need this, I would have
started with a Shipper's table (or some other noun representing outside
entitys with whom we do business), then I would have had a separate
table that allowed us to indicate the different business relationships
we have with that entity so that that entity could participate in a
variety of different sub-applications. Ahhhh . . . the beauty of
hindsight

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Sandra,
Have you considered putting a record in the Vendor Table for all
Employees who might also be Maintence providers? Thus, they are not
only an Employee, but are also a vendor. That way you have the joy of
the single MaintProviderID field AND referential integrity.


Good point - you probably need another field on your EquipMaintSched
for MaintProviderType (Internal or External). Then there are a
couple of different ways to handle storing either Vendor or
EmployeeID - probably the easiest would be to have two fields,
VendorId and EmployeeID then throught the application, require the
appropriate one depending on whether you select Internal or External
maintenance. This will allow you to maintain referential integrity
on either field without requiring both to be entered on a record.

Another approach is to use a single MaintProviderID field and let it
contain either the VendorID or the EmployeeID depending on the value
in MaintProviderType. The downside to this approach is that you
don't get referential integrity with Vendors or Employees. On the
plus side, you can require that there is a value in the
MaintProviderId field.

There are also other ways to do this but they might be overkill.

I'd be interested in hearing opinions on this from others since this
type of issue has been a common one in my projects and I've handled
it several different ways, never feeling that there is one surefire
absolute best way to handle it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott A wrote:
Pinch me!

Thanks for the validation - that's exactly what I had from
the begining!

One wrinkle that's making me scratch my (puny) brain:

Depending on the maintenance type selected, the service
will be performed internally or externally. For external
service, I want to store the VendorID (FK), but for
internal service, I would like it to be the EmployeeID.
What is the best approach?
-----Original Message-----
Funny you should ask, I'm working on something very similar right
now. I would say that you need at least three other tables -
MaintTypes, EquipMaintSched, EquipMaintLog

MaintType would contain all types of maintenance available

MaintType
--------------------
MaintTypeId*
MaintType**

EquipSchedMaint would have one record for each type of required
maintenance for a particular piece of equipment and the maintenance
interval for that maintenance item.

EquipMaintSched
----------------------
EquipId*
MaintTypeId*
MaintInterval (*you would have to define the units for the interval
days, weeks, months, years)

EquipMaintLog would have one record for each time a maintenance
item is completed on a piece of equipment.

EquipMaintLog
-------------------------
EquipId*
MaintTypeID*
MaintDate*
VendorId
Remarks

* is for PK fields
** is for unique index fields

Note that I didn't columns for NextMaintDate - technically this
should be a calculated field in a query since it is dependant on
the most recent entry in the EquipMaintLog. Depending on your
situation you might want to include this as a field on
EquipMaintSched - just keep in mind the potential pitfalls of
storing data that is dependant on other data. Actually, since
there may be times that you want to force a particular date for
maintenance you might have to have a field for it (hmmm . . .
thinking about my own project here).

Also, I would assume that there could also be a need for including
Vendor information regarding which Vendors do which types of
maintenance, which are approved for the maintenance items and which
is actually used on a particular date.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.

Scott A wrote:
I've got a table design issue that's stumping me - I'm
wondering if anyone has faced a similar problem and can
make a few recommendations as to how many tables will be
required to hold the data:

I've got one table with equipment records. This table
contains all of the details related to the piece (power
supply, name, type, etc.)...

Each piece of equipment is maintained according a schedule
(3 months, 6 months, 1 year), but can be maintained by
more than one person according to more than one schedule...

I need the database to record which types of maintenance
each piece is subject to, and the interval for which it is
maintained, and also track the dates when the services are
actually performed.

Any suggestions?

.

 

[John Vinson]

Customers are the same way!!

ah, but a business without any customers or any vendors would be SO
boring... not to mention providing a very limited income! <bg>

 

[Sandra Daigle]

;-) Good point!

 

[Lynn Trapp]

Yeah, I wonder how many different ways the Customer/Vendor AT&T has been
spelled in various tables around the world.

AT&T
A.T.&T.
A T & T
A. T. & T.
American Telephone And Telegraph
American Telephone & Telegraph

On and on it goes. I think we had at least 7 different versions at a place I
worked in Dallas.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Customers are the same way!!

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Oh, the joys of maintaining Vendors in ANY system. It has to be the
biggest nightmare ever created.

Hi Lynn,

I hadn't considered that approach - for this purpose it might be a good
solution but in the more general sense (the other similar issues that I
have) I don't like it since you end up with two records for each entity
that has the functional crossover and inevitably, information on one
will get updated without getting updated on the other (Normalization 101
I believe ;-)).

My worst case involves Vendors who are also Customers. In my case, the
problem is also due to project evolution. Originally we had Customers and
Vendors and there was never a time when Customer data would mix with
Vendor data - there was no overlap in the application. Then we started
logging inbound shipments and every shipment needed to be tagged with

the
Shipper -

well Customers are shippers and Vendors are also Shippers, how should I
represent this? Had I known up front that we'd need this, I would have
started with a Shipper's table (or some other noun representing outside
entitys with whom we do business), then I would have had a separate
table that allowed us to indicate the different business relationships
we have with that entity so that that entity could participate in a
variety of different sub-applications. Ahhhh . . . the beauty of
hindsight

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Lynn Trapp wrote:
Sandra,
Have you considered putting a record in the Vendor Table for all
Employees who might also be Maintence providers? Thus, they are not
only an Employee, but are also a vendor. That way you have the joy of
the single MaintProviderID field AND referential integrity.


Good point - you probably need another field on your EquipMaintSched
for MaintProviderType (Internal or External). Then there are a
couple of different ways to handle storing either Vendor or
EmployeeID - probably the easiest would be to have two fields,
VendorId and EmployeeID then throught the application, require the
appropriate one depending on whether you select Internal or External
maintenance. This will allow you to maintain referential integrity
on either field without requiring both to be entered on a record.

Another approach is to use a single MaintProviderID field and let it
contain either the VendorID or the EmployeeID depending on the value
in MaintProviderType. The downside to this approach is that you
don't get referential integrity with Vendors or Employees. On the
plus side, you can require that there is a value in the
MaintProviderId field.

There are also other ways to do this but they might be overkill.

I'd be interested in hearing opinions on this from others since this
type of issue has been a common one in my projects and I've handled
it several different ways, never feeling that there is one surefire
absolute best way to handle it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott A wrote:
Pinch me!

Thanks for the validation - that's exactly what I had from
the begining!

One wrinkle that's making me scratch my (puny) brain:

Depending on the maintenance type selected, the service
will be performed internally or externally. For external
service, I want to store the VendorID (FK), but for
internal service, I would like it to be the EmployeeID.
What is the best approach?
-----Original Message-----
Funny you should ask, I'm working on something very similar right
now. I would say that you need at least three other tables -
MaintTypes, EquipMaintSched, EquipMaintLog

MaintType would contain all types of maintenance available

MaintType
--------------------
MaintTypeId*
MaintType**

EquipSchedMaint would have one record for each type of required
maintenance for a particular piece of equipment and the maintenance
interval for that maintenance item.

EquipMaintSched
----------------------
EquipId*
MaintTypeId*
MaintInterval (*you would have to define the units for the interval
days, weeks, months, years)

EquipMaintLog would have one record for each time a maintenance
item is completed on a piece of equipment.

EquipMaintLog
-------------------------
EquipId*
MaintTypeID*
MaintDate*
VendorId
Remarks

* is for PK fields
** is for unique index fields

Note that I didn't columns for NextMaintDate - technically this
should be a calculated field in a query since it is dependant on
the most recent entry in the EquipMaintLog. Depending on your
situation you might want to include this as a field on
EquipMaintSched - just keep in mind the potential pitfalls of
storing data that is dependant on other data. Actually, since
there may be times that you want to force a particular date for
maintenance you might have to have a field for it (hmmm . . .
thinking about my own project here).

Also, I would assume that there could also be a need for including
Vendor information regarding which Vendors do which types of
maintenance, which are approved for the maintenance items and which
is actually used on a particular date.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.

Scott A wrote:
I've got a table design issue that's stumping me - I'm
wondering if anyone has faced a similar problem and can
make a few recommendations as to how many tables will be
required to hold the data:

I've got one table with equipment records. This table
contains all of the details related to the piece (power
supply, name, type, etc.)...

Each piece of equipment is maintained according a schedule
(3 months, 6 months, 1 year), but can be maintained by
more than one person according to more than one schedule...

I need the database to record which types of maintenance
each piece is subject to, and the interval for which it is
maintained, and also track the dates when the services are
actually performed.

Any suggestions?

.

 

[Mike Sherrill]

My worst case involves Vendors who are also Customers. In my case, the
problem is also due to project evolution. Originally we had Customers and
Vendors and there was never a time when Customer data would mix with Vendor
data - there was no overlap in the application.

There might not have been any overlap in the application, but surely
there was overlap in the database. Don't customers and vendors both
have addresses and phone numbers?

 

[Sandra Daigle]

Ah, a purist!!

Of course both have addresses and phone numbers!! That's not exactly what I
meant. Instead, I meant that initially, there was never a need for using
Vendor data in the same ways we use Customer data.

In this application, the Vendor information is skimpy at best (for the
moment and for the past 5 years). Vendors weren't even a consideration when
the database was in its infancy. Using the pefect hindsight (again) I wish
that when we added Vendors, I had taken the time to go with the
Entity/Entity Type approach (is there a better term for this??). Alas, I did
not . . . but that's reality when you have a production system that evolves.

 

[Mike Sherrill]

Ah, a purist!!

That's funny. Purists call me their favorite pragmatist.

Of course both have addresses and phone numbers!! That's not exactly what I
meant. Instead, I meant that initially, there was never a need for using
Vendor data in the same ways we use Customer data.

But that doesn't have anything to do with *database* design, right?
Functional dependencies are relevant; how the data might be used
isn't.

In this application, the Vendor information is skimpy at best (for the
moment and for the past 5 years). Vendors weren't even a consideration when
the database was in its infancy. Using the pefect hindsight (again) I wish
that when we added Vendors, I had taken the time to go with the
Entity/Entity Type approach (is there a better term for this??).

I'm not sure what you mean by Entity/Entity Type approach. "Vendor"
and "Customer" describe a relationship between parties.

Alas, I did
not . . . but that's reality when you have a production system that evolves.

I've known you through Usenet for a long time, and I have a lot of
respect for you and for your experience. But that's not "reality" in
any of the evolving production systems I've worked on since 1985.

 

[Sandra Daigle]

But that doesn't have anything to do with *database* design, right?
Functional dependencies are relevant; how the data might be used
isn't.

Ok - then let me restate - originally there were no functional dependancies
between Vendor and Customer data. Also, originally there was no requirement
for *any* Vendor data.

I'm not sure what you mean by Entity/Entity Type approach. "Vendor"
and "Customer" describe a relationship between parties.

Fine - I see your point. I read a book several years ago by Len Silverston -
"The Data Model Resource Book: A Library of Logical Data and Data Warehouse
Designs" which referred to parties and relationships between parties.
While this is clearly a very normalized solution, I'm not sure that it is
really necessary (or practical) to design all business applications this
way. As I've already stated - if I had had the slighest inkling of the
direction that this particular project would take, I probably would have
used this model. To implement it now would be significant undertaking.

I've known you through Usenet for a long time, and I have a lot of
respect for you and for your experience.

Thank you. Same goes for you

But that's not "reality" in
any of the evolving production systems I've worked on since 1985.

Ummmm . . . not sure how to reply to this . . . "well bully for you" comes
to mind

Seriously though, look around at the number of databases that have separate
Customer and Vendor tables. Are they all wrong?

 

[Mike Sherrill]

Fine - I see your point. I read a book several years ago by Len Silverston -
"The Data Model Resource Book: A Library of Logical Data and Data Warehouse
Designs" which referred to parties and relationships between parties.

Yes. "Parties" is a supertype; "customers", "vendors", "customer
account representatives", and "independent contractors" are subtypes.
They're not entirely different--they all have addresses drawn from the
same domain, they all have phone numbers drawn from the same domain,
and they all participate in many of the same relationships with other
parties. That they're not entirely different suggests a
supertype/subtype issue.

While this is clearly a very normalized solution, I'm not sure that it is
really necessary (or practical) to design all business applications this
way.

I'll assume you meant to say you don't think it's necessary or
practical to design all business *databases* this way. I don't agree.
I think the reasons are obvious.

As I've already stated - if I had had the slighest inkling of the
direction that this particular project would take, I probably would have
used this model.

Well, isn't one of the fundamental insights of the relational model
that database designers don't have the slightest inkling of the
direction *any* particular project will take? That's where
normalization shines.

I've been doing this stuff since 1985. Every database I've ever
worked on has been extended to do things the designers didn't imagine.

To implement it now would be significant undertaking.

The canonical procedure (grossly simplified) is to split the table
"Customers" into its supertype and subtype, rename "Customers", and
create an updatable view "Customers" that joins the supertype and
subtype and reflects the structure of the original base table. Add a
subtype table for vendors, and create a view "Vendors" that joins the
supertype and *that* subtype. (Don't know whether you've ever done
this before. If it's old news, consider it a bonus for lurkers.)

I'm curious why the original design would have made this a significant
undertaking.

This is surprisingly easy with Access. The Jet engine has better
support for updatable views than most server-based engines.

Seriously though, look around at the number of databases that have separate
Customer and Vendor tables. Are they all wrong?

If one company can be both a customer and a vendor, and that company
ends up with two different primay keys (one in the table of customers,
and one in the table of vendors), then it's wrong. Think about how
that kind of database might answer the question "What is <company's>
mailing address?"

 

[Mike Sherrill]

Yes. "Parties" is a supertype; "customers", "vendors", "customer
account representatives", and "independent contractors" are subtypes.

Did I write that? I definitely need more sleep.

"Parties" is a supertype; organizations and persons are its subtypes.

"Customers" usually expresses a business relationship between your
company and both persons and organizations; "vendors" usually
expresses a business relationship between your company and an
organization; "customer account representative" and "independent
contractors" are usually subtypes of "persons".

They're not entirely different

That is, organizations and persons are not entirely different.