How to limit permitted combinations in a junction table

[Remster]

I'm suffering from brain failure here. How, at the level of tables and relationships, do I limit the permitted combinations of values in SystemsContacts.Systems_ID and SystemsContacts.Contacts_ID to cases where Systems.Suppliers_ID and Contacts.Suppliers_ID contain the same value? The idea is that for each system there can be only one supplier and any number of contacts, but the contacts must work for the relevant supplier.

[orange]

I suggest you tell us about your "business" in simple, plain English terms.
Sometimes we get too close to the problem and lose perspective.
Also, just the process of writing the description of the "business rules" often clarifies things.
The rules of the business are reflected in relationships in your database.

[Micron]

I think I get it, but example data for allowed/not allowed would help. You'd handle this at the form level, not the table level, by using cascading combos. Your resulting contacts list would depend on what was chosen at the parent level.

[Remster]

I think I get it, but example data for allowed/not allowed would help. You'd handle this at the form level, not the table level, by using cascading combos. Your resulting contacts list would depend on what was chosen at the parent level.

Thanks, I can work out that solution easily enough. I'm just surprised nothing can be done at the level of tables and relationships. I think I've been away from Access for too long.

[Remster]

I suggest you tell us about your "business" in simple, plain English terms.

Coming up ...

[Remster]

So we have a list of all the IT systems used within our organisation, and I think the list needs to be converted into a simple relational database, as per my original post. Among other things, the list records, for each system, the supplier for that system, and the contacts at the supplier for that system.

Each system can have only one supplier, but each supplier can be the supplier for multiple systems. Each contact can work for only one supplier, but each supplier can employ multiple contacts. Finally, each system can have multiple contacts, and each contact can be the contact for multiple systems (crucially, two systems with the same supplier needn't have the same contacts).

Here's a sample set of data, in the current list format:

System	Supplier	Contacts
Davetronic I	Dave's Systems	Big Dave
Davetronic II	Dave's Systems	Big Dave Little Dave
Stevetronic	Steve's Systems	Big Steve

My puzzle is how, if at all, I can disallow the following combination at the level of tables and relationships (I know how to do it at the level of forms):

System	Supplier	Contacts
Davetronic III	Dave's Systems	Big Steve

[orange]

?? Do you have a requirement to identify a Contact for a Supplier who is NOT a Supplied System contact??

[Remster]

?? Do you have a requirement to identify a Contact for a Supplier who is NOT a Supplied System contact??

It's acceptable, but not necessary, for the system to contain contacts who are unrelated to any system. Those contacts will just be redundant. Does that answer your question?

[orange]

Does that answer your question?

Yes. I could not see any rationale for monitoring a Suppliers employees. That is, potential Contacts not related to one of your company's supplied systems.

It seems the only Contacts for which you have an interest is
-those Supplier Contacts who are System Contacts.

So I don't think you need a separate Contact table.

From your perspective your primary interest is your System
Consider System A that
- was supplied by Supplier X and
- has Contact Q as the current System A contact
- can have additional System A contacts

Each of the Contacts that you are concerned with could be identified through:
-given our System A
-who is/are the current SupplierContact(s) [for System A..]

[Remster]

I might have misled you (unintentionally, of course). The purpose of a separate contacts table is to record details about each contact (name, email address, phone number, etc.) in a single location. I appreciate that my simplified description of our requirements makes it look as if only once piece of data is recorded for each contact, viz. their name.

That said, I can now see that it doesn't much matter which supplier a contact works for: if someone is entering a record for system A, for which the supplier is supplier B, and they select a contact who works for supplier C, that's just a special case of selecting the wrong contact. They could select the wrong contact even if they were restricted to selecting contacts who work for supplier B. Is that your feeling?

[orange]

No, my concern was that you might be doing unnecessary effort maintaining a Supplier's employee list. You don't care who sweeps the floor or who updates security passes--your focus is the Contact/Contacts for the System you obtained from that Supplier.

Does this set up work for you?

[ArviLaanemets]

I'd have:
tblSystems: SystemID, SystemName, ...;
tblSuppliers: SupplierID, SupplierName...;
tblContacts: ContactID, ContactForename; ContactLastName, ...; -- This table will have full general contact info
tblSystemSuppliers: SystemSupplierID, SystemID, SupplierID, ...;
tblSupplierContacts: SupplierContactID: SupplierID, ContactID, ...) -- This table can have additional contact info specific for given supplier

[Remster]

Does this set up work for you?

Doesn't that allow for only one contact per system? We don't need to know who sweeps the floor or who updates security passes, but we do need to know who handles the contract, who manages the helpdesk, who's an expert in such-and-such aspect of the system, and so on.

[Remster]

I'd have:
tblSystems: SystemID, SystemName, ...;
tblSuppliers: SupplierID, SupplierName...;
tblContacts: ContactID, ContactForename; ContactLastName, ...; -- This table will have full general contact info
tblSystemSuppliers: SystemSupplierID, SystemID, SupplierID, ...;
tblSupplierContacts: SupplierContactID: SupplierID, ContactID, ...) -- This table can have additional contact info specific for given supplier

Out of curiosity, why would you include tblSystemSuppliers? I'd have thought that would be necessary only if a system could have more than one supplier.

[ArviLaanemets]

Out of curiosity, why would you include tblSystemSuppliers? I'd have thought that would be necessary only if a system could have more than one supplier.

Simply it is most general design! You can drop it, and add SupplierID field to systems table, but consider a scenario, where at some point you change the supplier for some system (e.g. previous supplier did go bankrupt). You can link a new supplier into systems table, but it means you lose all history, or your history will be wrong (attached to wrong supplier). Having tblSystemSuppliers table, with fields to identify links validity/validity period, allows to avoid such possible problems in future - and all this without any need to redesign the database.