Way around Max# of relationships?

[markmarko]

One of the elements we wanted in our DB is the ability to tell who entered
what data, so we are able to track where persistent problems occur.

Here's the brief overview example:
(Access 2007)
tblEmployees
tblSalesOrder
tblSalesDetails

tblSalesOrder & tblSalesDetails both a a field for "EnteredBy" which has a
relationship to tblEmployees.ID

When a user begins a session, they must 'log in' using a login screen I
created. Then, whenever they enter a sales order or sales detail, that record
gets stamped with their EmployeeID.

This is all working fine and dandy until now.. I've reached the max number
of relationships (32) on which tblEmployees can enforce referential integrity.

Is there a way around this max? If not, is there a better method to stamp
who entered which data?

 

[Jeff Boyce]

We aren't there, so we may need a bit more specific information to be able
to offer specific suggestions.

For example, if you have a field in tblSalesOrder that points back to the
tblEmployee to identify which employee assisted on the Sales Order, that
would require a single relationship. I don't see where you (or Access)
might be coming up with 32 or more?

By the way, if your tblSalesDetail has a foreign key pointing back to the
tblSalesOrder record that "owns" it, the EmployeeID in tblSalesOrder should
serve to cover the details... or are you saying one employee can start an
order, a second employee can handle one (?of many) Sales Details, a 3rd can
handle ...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

Do you have a field for each employee? If so, that is a serious design
problem. Your tblEmployee should look something like this:

tblEmployee
EmployeeID (primary key)
FirstName
LastName
etc.

Each employee has one record (row) in tblEmployee. Mark Marko may be
employee 1776. You would store 1776 in tblSalesOrder for Sales Orders he
created. This can be done a number of different ways, but as Jeff observed,
there is one relationship.

 

[markmarko]

Responses inline:

For example, if you have a field in tblSalesOrder that points back to the
tblEmployee to identify which employee assisted on the Sales Order, that
would require a single relationship. I don't see where you (or Access)
might be coming up with 32 or more?

My example only shows one relationship. There are many other connected to
tblEmployees, such as tblInstallOrder, tblInstallDetails, tblToolsIssued,
tblBonuses, etc. for stamping, as well as a number of tables that refer to
tblEmployees directly, such as Junction_SalesOrder_Employee. Put those all
together, and there are more than 32 relationships to tblEmpoyee.ID.

By the way, if your tblSalesDetail has a foreign key pointing back to the
tblSalesOrder record that "owns" it, the EmployeeID in tblSalesOrder should
serve to cover the details... or are you saying one employee can start an
order, a second employee can handle one (?of many) Sales Details, a 3rd can
handle ...?

That's correct. Other people may enter additional data later, and we need to
log who did so. Therefore, tblSalesOrders & tblSalesDetails must each have a
way to indicate who entered the data.

 

[markmarko]

Responses inline:

Do you have a field for each employee? If so, that is a serious design
problem. Your tblEmployee should look something like this:

Ha ha! No, I don't have a field for each employee. That would be quite
silly, wouldn't it? I'm sorry I wasn't more descriptive!

 

[Jeff Boyce]

There's a chance that your tblEmployee table is not well-normalized, leading
to (as in your case) 32 or more related tables. If you'll post back a
description of the fields in this table (and note those that are using up a
relationship), the folks here may be able to offer alternate suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

If you did it wouldn't be the first time somebody has done that sort of
thing.
Your follow-up post elsewhere in this thread provides some more details.
You wrote that some of the linked tables are:
"tblInstallOrder, tblInstallDetails, tblToolsIssued, tblBonuses, etc. for
stamping"
I'm not sure what you mean by "etc. for stamping", but I can say that
typically tblInstallDetails would be linked to tblInstallOrder. Even if
each detail record includes an EmployeeID, the Order and Details would need
to be related. I can see wanting to know which orders are associated with
which employees and vice versa, with the results displayed in a report or
something, but is there a similar need to display a listing of Details by
themselves? What I'm getting at is that some relationships may not be
necessary. As Jeff pointed out, exceeding the maximum number of
relationships suggests the design is not properly normalized. Many
databases track a lot of information about people, companies, etc. without
pushing the relationship limit, so I suspect a listing such as Jeff
requested could allow for some specific suggestions about how to tame this
beast.

 

[markmarko]

First off, here's the list of fields in tblEmployees:

-ID - Most relationship linked to this.
-PromoCode - (similiar to an employee number)
-OldPromoCode - Old version we are phasing out (included since some jobs
will still refer to the reps Old code
-FirstName
-NickName
-MiddleName
-LastName
-Suffix
-HomeRegion - relationship to tblRegions
-PositionCategory - relationship to tblCategoryList
-PositionSubcategory - relationship to tblSubcategoryList
-StartDate
-TechNumber - reference number assigned by client for technicians
-SalesRepNumber - reference number assigned by client for any person who
completes sales (can include techs)

I think that the table is set up well, do you disagree?

I don't doubt that the reason I'm pushing the limit of relationship is that
I want to know who typed in the data. The term 'for stamping' means 'to
indicate who type the data in to the database'.

tblInstallOrder is in a one-to-many relationship with tblInstallDetails
(same with install tables, etc).

So, of course, I want to indicate which person Installed a certain order.
So, for instance, tblInstallOrders will have a field [Installer] related to
tblEmployees.ID (It's actually through a junction table since it's
many-to-many (more than one tech can be on an order) but for simplicity we'll
say just one tech.)

That part is no problem. Here's the problem. I want to know who typed in the
order. I also want to know who typed in any given sales detail (and who typed
various other data). (Once entered, an InstallDetail is locked except for
notes and a checkbox to indicate if that details is to be included in order.
Therefore, if there's an error, user unchecks original detail, and creates a
new one. Since Install Details indicate who entered it, we can then determine
where the error are occuring. ) This has been a significant problem for us,
which explains why we want to be able to track it.

Here's a brief overview of workflow. Tech installs job, writes various codes
on paperwork indicating work performed. Data Entry Rep then enters all job
info, including those codes (They are the 'InstallDetails'). Then, a manager
goes through and checks all their data entry. Next, it's on to the accounting
dept. which does a final check. What's being checked for is: Did the tech
code it correctly in the first place? Did the Data Entry person make an
error? Did the Manager actually check it? All of these things have been
persistent issues with our pre-database system. Very difficult to see, for
example, if Jim the Tech was constantly coding wrong, or if Jenny the Data
Enterer was consistently giving the Install Tech she has a crush on more
codes he doesn't deserve, or if Sam the Data Enterer is consistently making
mistakes, or if the manager who claims he checking is quite obviously not.

Any given InstallOrder will also have a pre-call (to ensure customer will be
home, a postcall to check on customer satisfaction, and possibly a Quality
Control check. All three of those are things we want to track who made the
calls, who did performed the QC, so all of those need relationships to
tblEmployees.ID.

So tblInstallOrders has a relationship to tblEmployees.ID, to represent who
did the Installation, one for who did the precall, one for who did the
postcall, one for who QC'd. tblInstallDetails also has a relationship to
tblEmployees.ID , as does tblToolCheckout, as does tblToolsLeased, as does
tblHRcomments, as does tblBonuses, as does etc.... Add that all up, and
there's more than 32!

There may well be a better way to track who entered what data, and if there
is, I'm all ears.

 

[markmarko]

My response is under BruceM's posts.

 

[Jeff Boyce]

I took a look over the description of your Employee table.

It seemed like there were several fields that had more to do with
sales-related events than person/individual.

For example, a first name is something that "belongs" to a person (pretty
tight connection there).

Your:

TechNumber - reference number assigned by client for technicians
-SalesRepNumber - reference number assigned by client for any person who
completes sales (can include techs)

seem like they are more related to the role (or roles) a given individual
may serve in.

If true, could you have a person serving as a tech on one "sale" and
SalesRep on another? Sounds like this needs a new Person/Role table, with
the identifying "number" on that table.

Or maybe I'm missing the bigger picture...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[markmarko]

Anybody? Help?

 

[Jeff Boyce]

I didn't notice a response to my most recent response/question ... could you
describe the relationship between a person (individual) and the SalesRep #,
Tech #, PromoCode and some of those other fields that seem to have more to
do with sales than persons?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[markmarko]

My apologies, I didn't see your reply above...

Well, generally speaking, our Contractors (I've referred to them so far as
employees for simplicity, but they are really contractors) have 1 role. There
isn't a lot of 'changing', so I still think it's applicable to define those
codes per person. A given installer will always have the Client-Assigned tech
number. As for the sales, we have dedicated sales people, but also, the
installers, are free to sell services too, it often happens while on the job,
the customer will choose to add a service. The installer gets that sale, plus
they are free to sell on a referal basis. So any sales peeps, and installers,
will also have a Client-Assigned SalesRepNumber.

I'm not clearly seeing how changing that would help with the issue of having
too many relationships from the way I'm trying to track who entered which
data? Can you explain, please?

 

[Jeff Boyce]

I don't know if there is a relationship between these ... I was looking for
a more specific description to see if I could understand what you're
reporting.

What happens when you open up the relationships window? You might need to
click on the Show All button.

How many lines do you have connection tables?

Regards

Jeff Boyce
Microsoft Office/Access MVP