View Full Version : Relationship created new table?


khunter
12-10-2003, 01:47 PM
In the attached db I wanted to create several relationships between tables. After doing this, it created a table_1 table to show the links. Is this normal?

Pat Hartman
12-11-2003, 11:26 AM
Your relationships are pathological. Without knowing your application, I can't tell you exactly what the relationships should look like but - when relating tables, the primary key of one table is used as a foreign key in another table. Your diagram shows the tables being connectted both ways. One of the connections needs to be removed. A simple example is customer, order, orderdetails.

tblCustomer:
CustomerID (autonumber primary key)
etc.

tblOrder:
OrderID (autonumber, primary key)
CustomerID (long, foreign key to tblCustomer)
etc.

tblOrderDetails:
DetailID (autonumber, primary key)
OrderID (long, foreign key to tblOrder)
etc.

Notice that the orderID does not appear in the customer table. If you did that, a customer could have only a single order. Same for order. If the detailID were placed in the order table, you could have only a single item on each order. And finally, there is no need to include CustomerID in the details table since you would obtain it by joining to the Order table and then to the customer table.

khunter
12-11-2003, 11:40 AM
I have had problems with the relationships, that was the only way I could get what I needed without too much headache.

A description of the database: An engineer writes an ECR to modify one or more models. This being said; An ECR can have one engineer, one or more ADC (add/delete/change) that affect one or more models.

I have tried many times to construct many to many relationships to no avail. I even posted here for help, none found.

If you need more info on the database function let me know I apprieate the help and would love to create a solid application.

Pat Hartman
12-11-2003, 01:10 PM
The engineerID should be stored in the ECR table as a foreign key. Then you need a relation table that links ECR with Model. Download the many-to-many sample that I posted. It may help you to understand them.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany

khunter
12-11-2003, 01:38 PM
After taking a look at the sample many to many you posted I am now even more confused. If I create a fk with Engineerid in ECR
then how can I also have a linked table for models and adc?
I have tried to create this but I can not get any referential integrity with it.

Please take a look at the edited db and point me in the correct direction. As always, thank you so much for the input and your time.

I am really at a loss here as the improper way to design the relationships gives me what I need, but I can not get what I need with the correct design.

Pat Hartman
12-11-2003, 05:53 PM
I did not spend any time with the logic of this but you still had pathological (that's their real name) relationships. I removed them. You also had the forms set to not allow updates so even if you had managed to fix the relationships, you still would not have been able to update records with those forms. I fixed them also.

khunter
12-12-2003, 10:08 AM
I now understand how my relationships were pathological, however, I do not understand your new relationship fully. If I can attempt to clarify the logic maybe that would help.

The form should show:
ECR#1 was written by Kirt, it adds partnumber1 and deletes partnumber2 from modelA & modelB. (It should show the add partnumber1 and delete partnumber2, as well as modela & modelb)

ECR#2 was written by David, it changes partnumber3 from modelA. (It should show only the change of modelA)

The form or any variation I have tried with it will not display this info. The relationships do not show it either.

Once again, thank you very much for sharing your extensive knowledge with neophites like myself.