Strategy for groups within recordset

[BruceM via AccessMonster.com]

I couldn't think of a good, brief subject line for this thread. I have an
Employee database with EmployeeID (PK), FirstName, LastName, etc. Some
employees hold Certificate A, others hold License B, others can sign certain
documents, others can train new employees, and so forth. Some employees can
do several of the above. I had been adding Yes/No fields and such to the
Employee record for each category, but that is not a good way to go about it,
as each new "flag" field usually means multiple updates need to occur
elsewhere in the database.

I am looking for a strategy to manage the situation. One way, I suppose,
would be one-to-one relationships between Employees and a Certificate_A table,
a License_B table, etc., something like this:

tblCertA
CertA_ID (PK)
A_EmployeeID (FK)

The FK field would have a unique index.

Then for the Row Source of a combo box listing Certifcate_A holders:

SELECT tblCertA.A_EmployeeID, tblEmployee.LastName
FROM tblCertA
INNER JOIN tblEmployee
ON tblCertA.A_EmployeeID = tblEmployee.EmployeeID
ORDER BY tblEmployee.LastName

In practice there would be more fields, but that would not change the basic
idea.

I would need the CertA table for other things such as reports, etc.

Is this a reasonable approach? Am I missing something that could accomplish
the same thing without adding a new table each time (which seems in some ways
to be transferring the old problem to a new format)? Maybe I would have a
table of "extras" (CertA holders, LicenseB holders, etc.), and a junction
table between that table and tblEmployee. The more I think about it the more
I like that approach, but before I implement anything I would be interested
in hearing how others manage this situation.

 

[Stefan Hoffmann]

hi Bruce,

I couldn't think of a good, brief subject line for this thread. I have an
Employee database with EmployeeID (PK), FirstName, LastName, etc. Some
employees hold Certificate A, others hold License B, others can sign certain
documents, others can train new employees, and so forth. Some employees can
do several of the above. I had been adding Yes/No fields and such to the
Employee record for each category, but that is not a good way to go about it,
as each new "flag" field usually means multiple updates need to occur
elsewhere in the database.

Do you really need to store the attribute "has
certificate/license/document"? Or do you need additional information.

In the first case a simple attribute model may be sufficcent:

Employee: PK([Id]), [Name]
Attribute: PK([Id]), [Name]
Employee_Attribute: PK([idAttribute], [idEmployee])

So the existence of the record means has the attribute.

If you need to store additional information with your attributes, then
you normally should use one definition table per attribute holding the
specific values.


mfG
--> stefan <--

 

[BruceM via AccessMonster.com]

Thanks for the reply. I think that what you describe as an attribute model
is a junction table, which would be essentially a replacement for yes/no
fields in the main employee table. That is, the attribute either exists or
it doesn't. For instance, the person may train other employees or not.

However, some attributes do indeed require other information. A certificate
record may need a DateIssued field and such.

I think the "safest" is a table for each attribute. One of the yes/no
attributes may require more details some day. If the attribute is recorded
in the junction table I would need to break out the old records into the new
table, which would involve more redesign than adding a field to an existing
attribute table.

hi Bruce,

I couldn't think of a good, brief subject line for this thread. I have an
Employee database with EmployeeID (PK), FirstName, LastName, etc. Some

[quoted text clipped - 4 lines]

as each new "flag" field usually means multiple updates need to occur
elsewhere in the database.

Do you really need to store the attribute "has
certificate/license/document"? Or do you need additional information.

In the first case a simple attribute model may be sufficcent:

Employee: PK([Id]), [Name]
Attribute: PK([Id]), [Name]
Employee_Attribute: PK([idAttribute], [idEmployee])

So the existence of the record means has the attribute.

If you need to store additional information with your attributes, then
you normally should use one definition table per attribute holding the
specific values.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Bruce,

Thanks for the reply. I think that what you describe as an attribute model
is a junction table, [..]

Nope, not what I had in mind, take a look at this:

http://en.wikipedia.org/wiki/Entity-attribute-value_model

mfG
--> stefan <--

 

[BruceM via AccessMonster.com]

Thanks for the link. I studied it for a while, but I have to say I do not
understand enough of the article to evaluate whether the model is of possible
value. However, this reason in the article for EAV instead of conventional
row-modeling suggests it is much more than I need:

"The categories of data are numerous, growing or fluctuating, but the number
of instances (records/rows) within each category is very small. Here, with
conventional modeling, the database’s Entity-Relationship Diagram might have
hundreds of tables: the tables that contain thousands/ millions of
rows/instances are emphasized visually to the same extent as those with very
few rows. The latter are candidates for conversion to an EAV representation."

In my case there may be a half-dozen tables, or ten at the most; and the
tables would average maybe fifteen records, with a maximum of forty or so.

hi Bruce,

Thanks for the reply. I think that what you describe as an attribute model
is a junction table, [..]

Nope, not what I had in mind, take a look at this:

http://en.wikipedia.org/wiki/Entity-attribute-value_model

mfG
--> stefan <--