Create a relation between PK and FK.

[GhettoBurger]

I am currently using Microsoft Access to create a E-BookStore database, and have created 7 tables (Author, Book, Book Copies, Purchase, Member, Publisher and Feedback). No GUI is required its meant to be a simple database.

One of the database requirements is that members of the database can submit a score for the books that they have read (Score are from 1-10). I have tried many different ways of creating this relation but for some reason it doesn't seem to work.

The Pictures below will elaborate on the question even more.

[ItsMe]

You do not have to necessarily create a Relationship in order to store data for the members' feedback on books that they purchased.

I would start by getting the Foreign Key fields defined in your feedback table. Then, if you want to enforce constraints, the following video may be helpful.
https://www.youtube.com/watch?v=uKwLIvV_S9Q

[GhettoBurger]

I've watched the video and have also set a foreign key in the feedback table. I still don't understand how to connect members and feedback.

[ItsMe]

I did not watch the whole video so don't quiz me on it.

In your feedback table you will want to store the Book_ID and the Members_ID as foreign keys. You can have duplicate Book_ID's And you can have duplicate Members_ID's but you do not want a member voting on the same book more than once.

The following is a constraint

You can have duplicate Book_ID's And you can have duplicate Members_ID's but you do not want a member voting on the same book more than once.

Creating relationships in the relationships window will help you manage constraints and will help you enforce referential integrity. You will not be able to use the Relationships window and draw a line between table objects to manage the constraint I quoted above.

[GhettoBurger]

I created 2 foreign keys and added them Member and Book. Afterwards I connected them via Relationships (don't now any other way to connect PK and FK). Then this happened (Pic).


I want the user to give the feedback on the Feedback table, then for it to generate to the Members table.

[ItsMe]

...
I want the user to give the feedback on the Feedback table, then for it to generate to the Members table.

Hmmm, that may be problematic. Wouldn't you want to store the feedback information in the feedback table? Also, in the feedback table you can store the key value of the book and the member.

Are you familiar with queries? A query will allow humans to understand which member and which book is related to a given record in the feedback table.

[GhettoBurger]

No cant say I am familiar with queries. I've only started using Microsoft Access a couple of days ago. Would it be easier if I learned how to create queries ? If so I'll do that and put the database on hold for a day or two. (I would learn it regardless but I don't think ill be using Microsoft Access after this project).

[ItsMe]

For starters, you are trying to do something that is not the easiest thing to do in a relational database. This same thing is even more difficult to understand/comprehend. After you understand it, it is hard to retain.

Right now, I am wondering what information I should share with you and what information I should spare you the agony of being exposed to. Let me sleep on this and I will get back to you. I will say this. In order to manage data, you will need to understand RDBMS. In order to work with Access you will need to understand RDBMS. In order to be somewhat proficient in Access, you will also need to understand some basics in Application development.

[ItsMe]

It seems to me that you want to connect one entity with another. You want to connect Members with Feedback. You wish to do this via the Relationships Window and create a relationship between the two entities.

You can certainly do this, create a relationship between the two entities. However, this is not going to help you or your Users when it comes time to retrieve data from the tables. The purpose of the Relationships are maintaining Referential Integrity and Data Integrity by managing Constraints. You define rules for your database via the Relationships Window. These rules are the result of the Business Rules defined by operations and processes of a business or task. This is where the Relationship Window's job begins and ends.

When you enter data into your database, you need to respect the Business Rules. Hopefully, your database and your application is designed in a way that Constraints are defined to manage the Business Rules.

When you want to retrieve data from your database (tables), you will query the database. Access has a tool that will help you to create queries. You can create and save query objects using the Query Designer in Access. Queries rely on Joins to connect entities. When you retrieve data, you rely on queries and their Joins.

For me, when I begin a new project with Access, I start with the database design. I start by defining the Business Rules and defining the Entities. I then begin the process of identifying candidates for Keys. When I have all of my Primary Keys for all of my Entities defined, I continue the process of diagraming them in my Entity Relational Diagram (ERD). It is OK to draw an ERD on paper using a pencil.

To help illustrate, here is a very basic video that may get the wheels turning.
https://www.youtube.com/watch?v=-fQ-bRllhXc

You do not want to overcrowd your brain with information. However, it may be helpful to review this video on Keys. Keeping this information in the back of your mind as something you may encounter beyond the horizon cannot be a bad thing.
https://www.youtube.com/watch?v=_aN-8kszIdA