Hi guys I'm trying to implement database models using MySQL. One thing struck me when I was trying to "convert" an entity-relationship diagram (ERD) to relations (or SQL tables).
Note: Please keep in mind that a) "relationship" from the entity-relationship view and b) "relation" from the relational model are different constructs.
As far as I know, the following points are the standard when converting entity sets and relationships (as per the entity-relationship view) to relations (as per the relational model).
Entity Sets: Simply use all the attributes as columns (key attributes become the primary key).
Relationships (Many-to-Many): Use key attributes (from both entity sets) as relation attributes (i.e., table columns). So this means that when I'm trying to create a relation (table) for any relationship, I have to bring the key(s) from existing relations (tables) as a foreign key.
But when I looked up, almost everyone told me I shouldn't use a referenced foreign key as a primary key for that table (because foreign keys should allow non-unique values). This doesn't make sense because all kinds of relationship have to reference the keys from entity sets (relations).
I'm totally confused, somebody please help me!