many to many relationship

[jimfog]

I am constructing a table where it holds a many to many relationship with other 2 tables.

This many-to-many table has 2 columns, both of which are foreign keys to 2 other tables.

 

The many-to-many table is called favorites(it is self-explanatory what it does) and the parent tables are called business users and credentials.Here is an image:

https://skydrive.live.com/redir?resid=BE27434B2AAC8130!259&authkey=!ADptTU72dh1KYBc&v=3

 

As I understand, and tell me if I am wrong, none of these 2 columns must be primary key.

The reason being that a primary key must be unique in every row, but in a many -to-many table many times values are repeated throughout rows.

 

For example, many orders placed by the same customer(not related with my app).

 

So, am I right saying that none of the columns must be a primary key?

 

In addition, now that I am reconsidering things, maybe a many to many table is not needed, just one-to-many, from credentials to favorites.

Since the credentials table holds all the id's of the users of the site.

Edited August 28, 2013 by jimfog

[justsomeguy]

I'm not sure what the favorites table is for, what is the second column? Can a single user account have multiple sets of credentials? I'm not sure what the relationship is.

 

As far as primary keys, you can create a primary key that covers both columns.

[jimfog]

you were right, I was not so specific. This app has 2 types of users, regular and business, with their credentials(Id,email,password) held in the credentials table.

The rest of the details of these type of users, are held in 2 different tables, regular_users and business_users

 

Both type of users when using the site have the option of keeping favorites, the favorite item here is business user.

Meaning a regular user and a business user might have as a favorite another business user.

 

So my question revolves around the structure of a favorites table. So far, I have concluded to one-to-many relationship(FORGET WHAT I AM SAYING TO THE FIRST LINES ON THE POST ABOVE AND CONCENTRATE ON WHAT I AM SAYING NOW).

 

The favorites table will comprise of 2 columns, the one being a foreign key referencing to the credentials table and the second column(which will hold the favorites items) a foreign key also referencing again the credentials table.The reason that both columns reference the credentials table is that by definition the users that keep favorites and the favorites(being other users---business users) are located all in the credentials table.

 

How does the above structure sounds?Do you think there is a "hole" in the logic.

Do you want me to add more details?

[justsomeguy]

It sounds fine to me.

[jimfog]

There is one more thing that I want to ask and has to do with the logic describe above.

 

Do you think there are any problems by have a 2 cols table which are both foreign keys pointing to a col in another table?

Suppose there are not any problems from it. Do you think is redundant?

 

Here is an image that visualizes the relationship:

http://sdrv.ms/1fkMlbJ

[justsomeguy]

It's not redundant if that relationship is not represented anywhere else.