Hi
I am an account student and part of my assignment is to build access database for a bogus bike shop.
I have created the following tables
Suppliers, Customers, Products, Purchase Orders and Sales Orders
The way tutor said is that the relationship should be in the following way
Supplier to products (one to many) - products to purchase orders (many to one)
I have used Suplier Id (PK primary key) in suppliers and made a foreign key in products table (FK foreign key) but it seems that I get referential integrity error and I am unable to do it.
Then Customers to products (one to many) - products to sales orders (many to one)
Here I am
Facing a different issue
If I use customer ID as PK then I will have many of those customer IDs next to one product which won’t work as it won’t be unique and I will have to duplicate product data so that each customer transaction will have their customerID next to it.
I have ended up doing
Supplier - purchase order one to many and purchase order to product many to one, using a logic that one supplier can have many purchase orders and one product can be on many purchase orders
(I have used same logic on customers and sales orders)
But I was told it’s wrong
How can I correct the error Please?
Any help appreciated as I am stuck
I know this forum is for advanced questions and not student assignments but the tutor is refusing to tell the solution and I have exhausted tutorials and websites
Thank you in advance and also I am happy to share the file via one drive
I appreciate this is at very basic level and frankly useless but for some reason university decided that access is necessary for accountants lol