Many to many relation. How?

[Peter De Berdt]

On 8 Dec 2004, at 15:14, Robert Brenstein wrote:

>>
>> I think the serial number feature is an often needed one and it would 
>> be much easier, if Ruslan could add it as a new valentina feature :-)
>> Thanks,
>> Ralf
>>
>
> But recId is a serial number so to speak. It is automatically assigned 
> to each record and certain to be unique. The only thing is that newer 
> records may get lower number of some records were deleted earlier. But 
> for unique identification, recId works just fine. Any other definition 
> of serial number is up to Valentina programmer. What you consider a 
> serial number, I may not. In a program like FileMaker, an explicit 
> serial number feature is needed because as a programmer you have no 
> access to internal numbering.
>
Indeed, and in fact, the auto-incrementing serial numbers should not be 
used to identify a record to the user. If you want to incorporate 
record numbering, do it yourself, the way you want it to. The fact that 
you have no immediate access to the recID numbers (i.e. write access) 
is a good thing, it garantees the uniqueness and integrity of your 
data. If you implement your own auto-incr. scheme, you can allow the 
user to change the next ID number for example without every having to 
fear double IDs. You've taken a step above the "beginners" database, 
like Filemaker is and have chosen speed and reliabity instead of ease 
of programming, try to think "non-Filemaker".

I started using an SQL database, now I'm back to fulltime Filemaker 
development in my daytime job, I have found out how easily Filemaker 
forces you to use non-conventional relationship techniques (I still try 
to avoid them as much as possible, but sometimes you don't have a 
choice). Just to say I understand your difficulty adapting to a more 
uniform and structured way of database design.

I don't recall the start of this thread, but many to many relations 
(that's what I'm reading in the subject) are done best by creating an 
extra table to hold the recIDs (well, ObjectPtrs actually):

TABLE MANYTOMANY
MyTable1ObjectPtr (pointing to RecID from table 1)
MyTable2ObjectPtr (pointing to RecID from table 2)

Example records:
MyTable1ObjectPtr			MyTable2ObjectPtr
1						1
1						2
1						3
2						1
2						3
3						1
3						2


Best regards

Peter De Berdt