Good afternoon everyone,

This is my first post to the Utter Access forums. After searching the web for a useful database design site, this is far and away the best I've come across.

I am trying to design a database for use in our office (an architecture firm) to track products that we use in our buildings, such as tiles, water protection systems, siding, windows, and such.

The design I have worked out mainly uses three tables: Products --1tm--> Companies --1tm--> Contacts. A Product can be linked to multiple Companies (such as a manufacturer, distributor, fabricator), and in turn each Company is linked to multiple contacts (product rep, installer, company president).

The problem I am having is this: I want to be able to see the related Companies and Contacts for a particular Product, but I want to see ONLY the related Contacts from that Company. In other words, a Contact might have multiple Products he represents. But in my current form view, the Company shows ALL Contacts related to the Company. I want to build my form so that when a Product is linked to a Company, only the Contacts related to that Product are displayed in the form.

I believe I might have to use a junction table, but I am trying to think of ease of user (not me) input in the form. I hope I am stating the problem correctly, any help would be appreciated immensely.

Well, in order to do what you want to do, you need some meaningful way of connecting Contacts with the Products they sell. Trying to relate Products to Contacts through your Companies table requires that you use subqueries or some other form of filtering criteria.

Why is it more important to relate so many different contacts with a Product -- why do you need to know who the President is, or who the installer is in your database? Couldn't you just include that info on an invoice or tracking sheet when a particular individual is contacted about his/her product?

While unusual, perhaps you should relate Products to Contacts, and have a secondary join on Contacts to Companies:

Products -->Contacts-->Companies

Lastly, I wonder if your schema wouldn't be better served by adding an additional table: Manufacturers.

Product-->Manufacturer, Product-->Vendor-->Contact

Products Manufacturers Vendors
-ProductID -ManufacturerID -VendorID
-ManufacturerID -Contact -Contact
-VendorID

In this manner, an Installer, Sales Rep would all be Vendors, and each with a separate Contact field. A Manufacturer, fabricator would have their own contact field.
To have multiple Vendors for a Product, you would need a join table:

ProductVendors
-ProductID
-VendorID
-VendorProductPrice
-ProductPricingDate

Does this give you any ideas? Let me know.
-Kurt

That schema didn't turn out too well on the post. What I meant to illustrate was:

Products
-ProductID
-ManufacturerID
-VendorID

Vendors
-VendorID
-Contact

Manufacturers
-ManufacturerID
-Contact

ProductVendors
-ProductID
-VendorID
-VendorProductPrice
-VendorProductPricingDate

Products 1tm --> ProductVendors <--mt1Vendors

That should be a little clearer.

Kurt

> Why is it more important to relate so many different contacts with a Product -- why do you need to know who the President is, or who the installer is in your database?

The architects need know, for example, the manufacturer of a particular window in case they have questions about its specs, and also the installer of that window for questions about installation. Possibly even a distributor as well. And they all may come from different companies.

To take the example further, those companies may have different divisions to represent different products. Anderson Windows may have a rep for their aluminum windows, another for their steel windows, another for safety glass windows. If I pull up a record on Anderson aluminum windows, I want to see the only person from Anderson that reps aluminum windows, not their steel and safety glass people.

To further muddle the picture, that Anderson aluminum windows person may rep MORE THAN ONE product. Another thing this database should do is if I pull up the Anderson aluminum window record, and let's say I update that rep's phone number,I should only have to update that number once.

Thanks so much for your reply Kurt, I will look over your structure and see if I can get it to work the way you suggest.

Here's a logical model for a deisng I would use.

Both Companies and Persons are a type of Party.
A product can be related to multiple parties.
Each relationship includes a type code to tell us the nature of the relationship (manufacturer, vendor, etc).

You can add a table to relate two parties together. Include a PArty Relationship Type code.

hth.

mcp

But a Party can relate, in turn, to multiple Products as well (representatives of more than one product)

Maybe a should have a Relationships junction table, bringing in ProductID, CompanyID, and ContactID. Though this does not make for easy user entry. Wouldn't you have to enter in all the information separately (or in a multi-table form) and then create the relationships by setting up a relationship by another table where you select the Product, Company, and Contact. I foresee multiple relationships per Product and this would get confusing.

Maybe it would help to look at my database so far. It is attached.

Jon,

I haven't had the opportunity to look at your database yet, but in answer to your question about join tables, the easies option is to ask your user if they wish to add an additional item (say, a Vendor Contact) to the Product they're viewing. When the answer is yes, you take that particular ProductID, and with a Combo box looking up Vendors in your Vendor Table, INSERT the ProductID and the VendorID from the combo box into your Join table. Now you have a Product related to Vendors through the ProductVendor table. (A direct relationship to the Vendors table, depending on how you've built this database to begin with, may still exist, which is fine. If there is only one Contact per Product, then there is no need, at that time, to have an entry in the ProductVendor table -- only when there is to be more than one.) You want to make sure that when there is a multiple Vendor relationship, your users have the ability to view those.

Any help?
Kurt

I follow... but then the question becomes how can you join both types of relationships in the same form?

You can do it a couple of ways, a) in the background, using an INSERT statement, or b) Using a pop-up form

What I've done in the past, is to create a join table without a primary key on the join fields. Even though they are mtm, as long as your logic is sound, it isn't necessary.

When my user is on the Products form, there is a button that says "Add another Vendor for this item."
When they click on the button, they're presented with a small popup form that has a list box whose recordsource is something like
"SELECT tblVendors.VendorName FROM tblVendors INNER JOIN (tblProductVendors ON (tblVendors.VendorID = ProductVendors.VendorID) INNER JOIN tblProducts ON tblProductVendors.ProductID = tblProducts.ProductID) WHERE ProductID = " & Me.ProductID

Below the list box is a Combo Box (cboVendors) whose rowsource is "SELECT VendorID, VendorName, VendorCity.... FROM tblVendors"

In the AfterUpdate event of the Combo box, a message to the user asks if he would like to add this vendor to the list of suppliers or contacts for this product. If the answer is yes, Run your Insert statement:

strSQL = "INSERT INTO tblProductVendors(ProductID, VendorID) Values(" & Me.ProductID & ", " & Me.cboVendors.Column(0))"

db.Execute strSQL, dbFailOnError

Finally, requery the list box to show the newly added Vendor for this product. What do you think?

Kurt

what happens if there is an existing Vendor that needs to be related to the Product? I'm asking myself so many questions about this thing...

I've asked the individual who will be the main user for the database to mockup some forms for me to see how he envisions it. Maybe that will help me visualize the needed relationships.

The model provided allows a many to many relatioship between parties and products. You just need to define any unique keys based on your rules.

For example, if a product can only have one company related to it for each relationship type (ie: 1 vendor, 1 mfr, 1 distributor), then make a uniq index on product and ProductPartyRelationshipType.
If a company can only serve in one capacity for a product (i.e., distributor, but not vendor). then create an index over product and company. If there are no limits, then you don't need a unique index at all.

mcp

Avoid create a table that resolves many to many relationships between more than 3 tables. It is almost guaranteed to violate Boyce-Codd Normal Form, as well as 4th and 5th normal forms, resulting in redundant and possible inconsistent data.

Having a join between tbl_products and tbl_companies suggests that each company will only have one product. Since you are suggesting that not only will companies have more than one product, but different people responsible for each product, I would suggest removing the join between tbl_products and tbl_companies, and instead, similar to what Kurt was saying, joining tbl_products and tbl_people via a junction table so each product can have many people responsible for it, and each person can have many products. The join between people and company allows you to build a list of products per company.

This junction table will also allow you to enter a relationship type, such as whether the person is responsible for vending, manufacturing, installing, etc. The table structure would include

productID
peopleID
relationship

On the form, this can be shown in a subform showing the list of contacts for each product, and the relationship type. This subform would be bound to the junction table, so you can directly add new contacts for the product. You would also be able to show all of the company details with each contact. You can use the notinlist event to add new contacts - this would pop up an 'add contact' form and possibly also an 'add company' form.

You may also want to move your 'keywords' into a keywords table, and again use a junction table to relate these to the products - this would allow for easier searching of the keywords or product class.

Does that help at all?

Try the attached db.

I made a few mods to the model I proposed. I added a ProductContact table to list the contacts for a product and a productcompany table to list the companies for a product. I removed productid from company.

I altered your forms so that the form/subform relationships include product id.
I also had to change the records soruce for the forms.

You cannot add new contacts through these forms. I think you can add a new company for a product though. I haven't tested it much. Just trying to provide some ideas.

You'll need code to allow adding a contact. What I would do is put an Addcontact button on the compay subform (which is now really CompanyProduct), When the user clicks it, open a separate form in dialog mode that contains a list box of all the employees for the company, a Save, and a Cancle button. If the user selects a contact and clicks save, use code to add the contact to the productcontact table. Sicne you only show contacts for the company, you should be OK.

On risk is that if someone gets fired or quits. Removing them from the company table but leaving them in the contacts table will make your data inconsistent. So you need to do some cleanup if a company changes for someone. However, the form is deisnged such that the inconsistent records will not show up in the form.

hth.

mcp

There is one more option, but it doesn't really contribute to a normalized structure, but it might be the way to go. As you know, you can create a query that relates two sets of data on any field, provided that the field values are consistent.

During any interraction between a Contact person and a product, you could drop that information into an ad hoc table, so to speak, with one caveat - the contact person would have to exist already in the database, with a unique ID, or be added right then an there. When an invoice was generated, an order placed, or an RFQ, insert that contact into the form that is generated from the transaction.

Now your data isn't normalized in the sense that you can have many different contacts in the database, without a hard join relating them to your products, but a record of the transaction and the Contact name would exist, and later on down the road, you could revise the table of invoices and create your relationships.

I think the danger here, is that because of your desire to have your database be as flexible and powerful as possible, you may be losing sight of the priorities. I think your idea of talking to your architects and seeing what they need or want is a great step back to the road of objectivity. We've all been down that other road, where we begin building something, and it functions very well. Then we see where we can embellish on an already good design, and we end up with tentacles stretched to every possible direction. While our project is still flexible, it has way too many loose ends.

Here's a way of looking at your product and seeing if your perspective changes a little. In Unified Modeling Language, a software system is initially described not by what it's supposed to do, or the need it's going to fill, but by what happens in the real world, completely separate from using the software. Write up a scenario of a couple of ordinary transactions in your office:

"A client expresses a desire for a style of window. The architect examines catalogs for available models that might satisfy the client's desire. If a match is found, the architect calls one or more suppliers, and contacts a sales rep. The sales rep sends more information. After several meetings, the architect enters into an agreement with the Rep to purchase the windows" -- okay up to this moment, the Contact information is just incidental information that could be stored in a Vendor table. Not until the transaction initiated is the Contact now a necessary piece of information to be related to the products in the database. Not to get off track, here, but that is the type of scenario I'm talking about. Leave the software or database alone for a while and build your real world scenarios. You may see where things begin to get more complicated than necessary, and where the real need arises.

Forgive me if I've told you things you're already familiar with. I know for me, I often have to reign myself in.

Kurt

You need two relationships: Product to Company and Product to Person. Both are many to many.

If you limit yourself to identify the companie for a product by going through the contacts, you can't store the product company relationship unless you have a contact. I can imagine many instances where there is no direct contact. You just call the 800 # and get whoever answers.

If you want to store any information about the relationship between company and product (such as the relationship type), you have no where to do it, except in the Product People relationship, which means it wil be duplciated and possibly get out of sync.

BCNF requires that you have two relationship tables to do this.

Very nice, mcp. You've done this before -- CSI numbers and all. Nice direct design.

Kurt

Thanks. I do this everyday. I'm current modeling a data warehouse for a large logistics company.
What's CSI?

CSI (Construction Specifications Institute) is a industry standard that assigns a number from 00001 to 16999 for classifying building materials and methods.

I sincerely appreciate the efforts on your part to help out this dewbie (database newbie).

Why are you asking me? It's in your database! The CSI I'm familiary with are the Construction Specification Institute numbers for Hotel/Facility/Industry construction and maintenance contracts. I just commented because it seems that those are the types of numbers you are using.

I like the entry form. Very nice.

One thing I noticed in your tbl_People is that there were multiple entries for the same person... I think that will be a problem to update a contact multiple times. Or maybe you just copied and pasted the same data to populate the table?

Shame on me for my last post. I didn't see who the question was addressed to. Sorry, guys. Sounds like you've both got things moving along.

Yeah, I just took Jon's db and altered it.

I struggle on a daily basis with modeling people.
The problem is that there is no universal identifier for a person. There are 9 James Lees in the Portland area where I live.

SSNs are unique, but not universal.

It is a problem to find a natural identifier for a person unless we are going to start identifying ourselves by dna codes.

I general try a match on last name and first initial and then check other things like phone numbers etc. In the end, I provide users a short list to choose from before creating a new record. Some things are still best left to the human mind.

mcp