[TYPO3-mvc] mmm-relationship

[Barbara Schwarz]

There is a problem with my implementation:

I have defined a new entity and table "partner" which has two foreign 
keys: a company_uid and role_uid. With each product I can associate many 
partners. It should not be possible to create two partners with the same 
pair company_uid, role_uid. In order to enforce that, I have defined the 
pair (company_uid, role_uid) as UNIQUE.

Here is the scenario shows the problem:

1. I create a new partner, uid=1

uid	company_uid	role_uid	deleted
1	3		4		0

2. I delete this partner (deleted set to 1):

uid	company_uid	role_uid	deleted
1	3		4		1

3. I create a new partner, but with the same pair company, role:

uid	company_uid	role_uid	deleted
1	3		4		1
2	3		4		0

And this causes a SQL error, because the pair exists already!

A small change helps a bit: I define the triple (company_uid, role_uid, 
deleted) unique, but the solution is not really good: I will be able to 
insert the second partner, but I will not be able to delete it:

uid	company_uid	role_uid	deleted
1	3		4		1
2	3		4		1

Does anybody have an idea how to solve this? Is it possible to force a 
record removal from a table instead of just setting the field deleted to 
1? Is there a way to say: if deleted == 0, the pair company_uid, 
role_uid must be unique?


Barbara



Barbara Schwarz wrote:
> Yeah, that's exactly what I'm planing to do. Good to know that you're 
> using it already and that it works.
> 
> Thanks a lot!
> 
> Franz Koch wrote:
>> Hey Barbara,
>>
>>> Yes, the solution with the "producer" field in the product class does
>>> not fit my project's needs because a product can have many producers (as
>>> strange as it sounds, it's true). And the set of possible roles should
>>> be dynamic too.
>>>
>>> So, your second solution is actually what I want, but when you think
>>> about the table of the "WhatEverNameFitsInHere" object, it would be
>>> something like a mmm table (which do not exist).
>>
>> I'm actually using that solution already by simply using IRRE for 
>> that. Ported to your extension I would have added a IRRE field to your 
>> product table (maybe called "relations" or something) and map the 
>> "WhatEverNameFitsInHere" table to it (via parent_field etc). In the BE 
>> you then can add the company and it's role then.
>>