If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Strategy for groups within recordset
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200911/1 |
#2
|
|||
|
|||
Strategy for groups within recordset
hi Bruce,
BruceM via AccessMonster.com wrote: 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 -- |
#3
|
|||
|
|||
Strategy for groups within recordset
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. Stefan Hoffmann wrote: 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 -- -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200911/1 |
#4
|
|||
|
|||
Strategy for groups within recordset
hi Bruce,
BruceM via AccessMonster.com wrote: 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-...te-value_model mfG -- stefan -- |
#5
|
|||
|
|||
Strategy for groups within recordset
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. Stefan Hoffmann wrote: 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-...te-value_model mfG -- stefan -- -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|