Multiple Database Records with Same Primary Key

Question

I need to store relationships where a single attribute_id may be visible in multiple store_id and I was trying to use attribute_id as the primary key. It would be something like this:

+--------------+----------+
| attribute_id | store_id |
+--------------+----------+
| 147          | 1        |
| 147          | 2        |
+--------------+----------+

But Magento doesn't see that with good eyes as it returns the exception Item (foo) with the same ID "x" already exists.

What is the best solution for that problem?

Answer 1

Here is what I did, I created a relationship id column (like @magefms recommended) which happens to be the primary key, and whenever I need to update this relationship, I delete all the records that contains an specific attribute_id and create it again. To do that I implemented the following function in my model:

public function deleteByColumnValue($column, $value)
{
    $collection = $this->getCollection()->addFieldToFilter($column, $value);
    foreach($collection as $item) {
        $item->delete();
    }
}

This function is probably available somewhere across Mage_Core, but I couldn't find it. Anyway, then on the controller save action, this is how I call it:

Mage::getModel('module/model')->deleteByColumnValue('attribute_id', $storeId);

Hope this is helps someone!