Search code examples
mysqlsql-serverdatabaserelational-databasepowerdesigner

Is it a correct procedure to link a Relationship Table with other tables?

I would like to know if it is right to link a relationship table with other tables. I tried it in PowerDesigner, but it give me a warning about it. However: I have two table called "Client" and "Staff" in Many-to-Many relations. In the given relationship table I linked two other tables in 1-to-Many realtions. The relationship table now has two primary keys (primary key of staff and primary key of client) and 2 foreign key. It's a correct procedure? I have this situation: enter image description here

The warning message is:

Category Check Object Location Table Index Index inclusion Index 'generate rent history.GENERATE_RENT_HISTORY_PK' includes 'GENERATE_RENT_HISTORY_FK' ::generate rent history

Solution

  • It's valid for a table to have a composite primary key which consists of two columns, and for each of those columns to be a foreign key. And it's valid to have other columns which are also foreign keys.

    I'm not familiar with PowerDesigner, but I suspect the warning message isn't about the foreign key constraints, but concerns redundant indexes.

    To support the PRIMARY KEY constraint, there's going to be UNIQUE INDEX on (idstaff,idclient).

    To support the FOREIGN KEY constraints, we would need an index with leading columns of idstaff, and another index with a leading column of idclient.

    But an index on (idstaff) is redundant.

    We already have an index with a leading column of idstaff... the primary key index.

    As far as the table definition, these two indexes are sufficient to support the foreign key constraints referencing staff and client:

     PRIMARY KEY (idstaff,idclient)
 KEY generate_rent_history_IX1 (idclient)

    There's no need to add an index like this:

     KEY generate_rent_history_IX4 (idstaff)

    I suspect PowerDesigner is being instructed to generate that redundant index, and is issuing a warning that the index is not actually needed.

    (I'm just guessing at the order of the columns in the PRIMARY KEY. If the columns are the other way around with idclient as the leading column, then we need the index on idstaff column, and the index on idclient column would be redundant.)