Primary/Foreign Key EER Diagram Questions

[blyxx86]

Good Afternoon Everyone,

I am migrating from MS Access to MySQL (TONS faster, not to mention the usefulness of triggers).

Well, I am running into problems with the way MS Access lets you design and the way MySQL designs tables. I test the joins and relationships with an ODBC Connection and linking the tables from within MS Access to my MySQL database.

My question is within the PK-FK relationships.

I usually would have one UNIQUE/Auto-Incremented id being created for most tables. However, when I create this type of relationship with certain tables I am unable to produce a truly enforced one-to-many relationship.

I am attaching the photo of the relationships I am concerned with presently.

Customer 1:N Customer_Model
Customer_Model 1:N Customer_Model_Detail

Customer_Model requires a UNIQUE index between Customer_id and Custom_Name, but I don't think that needs to be the PK for the table. I believe it would be simpler if I could generate a PK with an Auto-Increment option as this would solve my issue of having both [Custom_Name] and [Custom_id] as FK within Customer_Model_Detail.

When I do as described above with the Auto Increment PK, MS Access will not recognize the relationship between PK [Customer.Customer_id] and FK [Customer_Model.Customer_id] and instead of saying 'One-to-Many' within MS Access it shows as 'Indeterminate'

However, the relationship PK [Model_List.Model_List_id] and FK [Customer_Model_Detail.Model_List_id] always shows up as "One-to-Many" within MS Access.

Please help!

Thank you,
Kyle

[blyxx86]

As an addition...

Is this perhaps just a limitation of MS Access to read the MySQL table schema properly?

Is there a decent front-end application that I can use to sample the database structure that will recognize the relationships properly?

Below is the database as I THINK it should be designed.

[blyxx86]

I think I solved the issue.

There may have been some strange setting in a key, because when I deleted the tables and started over they were able to join properly.

Everything seems to be solved.

Thank you!