two one to many relationships between the same two tables?

[neha]

I'd really appreciate a second opinion on the relationships I've created between three tables. I'll make my problem really specific by describing my database design

ProductTbl
ModelNo (primary key)
Name
ManufacturerCode
DealerCode
(other fields containing product details, like price, size etc)


Manufacturer/DealerTbl
CompanyCode (primary key)
Name
Address
PhoneNos

AddressBookTbl
IDNo (primary key)
CompanyCode
Name
Designation
PhoneNumber

(The purpose of the address book table is so that all persons and contacts are stored in one easy to access table, that contains only names and numbers and no company information. It is also so that I can save multiple contact persons for the same company)

Manufacturer/DealerTbl (Company Code) is related to ProductTbl (ManufacturerCode) by one-to-many relationship.
Manufacturer/DealerTbl (Company Code) is related to ProductTbl (VendorCode) by one-to-many relationship.
AddressBookTbl(CompanyCode) is related to Manufacturer/DealerTbl(CompanyCode) by one-to-many relationship

So there are two one to many relationships between the same two tables only these are linked by different fields. I've created the relationships, even made subforms based on them for data entry and they all seem to work fine.

However, I have never come accross a situation where there exist two relationships between the same two tables. It may seem to work fine now but will it cause any problems in the future?

Please advise.

 

[speakers_86]

It looks like this is only because you have vendors and manufacturers smashed into the same table. If they are different they should be in different tables. It appears they are different, because if they were not, you would not need both relationships. I recommend either treating them as the same thing and erasing one of the relationships, or separating vendors and manufacturers, the latter being more likely.

After rereading, it looks like you smashed those two tables together simply for an easy to build address book. I think that is where your problem actually is. In the form for your address book, the CompanyID field can be based on a union query of ManufacturerID and VendorID. Now you can seperate these two different tables. I've had this issue before too, and it took me a god awful long time to figure it out.

 

[stopher]

However, I have never come accross a situation where there exist two relationships between the same two tables. It may seem to work fine now but will it cause any problems in the future?

Having multiple relationships is fine imho. In fact, having a single contact list is the approach I would recommend.

When you create multiple relationships in the relationship window you need to add the table twice. The second time you add it, it will be given an alias name. In fact you can change the alias names of both contact tables so one table can be Maufacturer and the other Dealer. By doing this it is easy to refer to the address for themanufacturer rather than the address for the dealer.

The only bit I don't quite get is why you have three tables. Why do you have Manufacturer/dealerTbl and AddressBookTbl?

By the way, you have / in the tbale name. I'm not sure that's a good idea.

Chris

 

[neha]

Dear Speaker 86,

Initailly, I had started with two tables. I combined them for two reasons-

1. some manufacturers are also vendors which was leading to unnecessary duplication of entries. It also played havoc with data entry, I had to enter the same data twice, once into the Manfacturer table and once into the vendor table, when essentially they were the same company and both the tables had the same fields.

2. Yes, I did it for the sake of having an address book. could you explain to me how the combined ID field would work? It might be helpful in the future.
Thanks!

 

[neha]

Dear Chris,

For some manufacturers/dealers there are multiple persons you can contact, sometimes as many as 5 in the different company departments. I could not think of any other way to be able to add many contacts to the same record in the Manufacturer/DealerTbl other than making a seperate table for contacts and having a one to many relationship. Hence, the address book.

Thanks for the tip, I'll remove the '/' from the table name. What problems could that cause?

 

[neha]

Dear Chris,

How do I change the alias names of the tables? I tried right clicking the tables in Relationships but didn't see any option for renaming them.

Neha

 

[stopher]

Dear Chris,

How do I change the alias names of the tables? I tried right clicking the tables in Relationships but didn't see any option for renaming them.

Neha

Sorry, I meant the query design window. Just add the table twice. Then right-click on one of the tables and you see the alias for that table.

 

[speakers_86]

Here is a quick example of how you could build your address book with a union query. It's a bad example, but you can see how it works. In the sample I am storing the company code as text, but that's not really how you would want to do it.