Many to many relation. How?

[Ralf Sander]

Hi Ruslan,

> On 12/7/04 2:28 AM, "Ralf Sander" <ralf at end-if.de> wrote:
>
> Hi Ralf,
>
>> In FileMaker, a many to many relation can be build by storing many
>> related values in one record separated by returns.
>>
>> e.g.
>> tablea, field "ids"
>> record 1:
>> id1
>> id2
>> id3
>>
>>
>>
>>
>> tableb, field "id"
>> record 1:
>> id1
>> record 2:
>> id2
>> record 3:
>> id3
>>
>> questions:
>>
>> 1. Is this possible in v, too?
>
> 1) first of all. This is NOT STANDARD way for Relational model.
>     I mean FileMaker.
>
> 2) if you very want you can store several IDs into some field.
>     this can be String, VarChar, BLOB or TEXT field,
>     up to you.
>
>> 2. How will the select statement look like?
>
> That is the problem. This is not correct way for RDBMS,
> So there is no query in SQL for this.
>
>> 3. In FileMaker, there exists a limitation for the lenght of a single
>> relation string (32 bytes, I think) Is there a limitation in 
>> Valentina?
>
> Limitations of each field type describe in the ValentinaKernel.pdf
> BLOB and TEXT do not have limit on size.
>
>> 4. How can I produce save unique values in a server invironment?
>
>> You will say: Use object pointers and a third table, but that does not
>> produce save ids.
>
> What you mean "save ids" ?
>
> You mean CUSTOM GENERATED values ?
> Then you should self generate them.
Yes, but how can I produce a serial number, that is really unique?
May be a silly question, but how can I prevent, that 2 users create the 
same serial? I could use usernames in the id, but the longer the string 
the slower operations will be, right?


>
>> If I delete a record in table b, another record will
>> take it's place, right?
>
> Right. But if you use ObjectPtr with option CASCADE,
> Then all linked records will be deleted also automatically.
> So there is no reference integration failure.
Yes, but I want to keep related records alive.
>
> * Finally, in RDBMS model, to get M : M link you must to have third 
> table.

OK, lets assume, I have 3 tables.
t1:
1 John
2 Bob


t2:
1 personid1 taskid1
2 personid1 taskid2
3 personid1 taskid3

t3:
1 task1
2 task2
3 task3

How will the select statement look here, if I want to get all tasks in 
t3, that are related to record 1 in t1?

Thanks,
Ralf