I cannot figure out how to create a many to many recursive relationship. I’ve created the recursive relationship but I don’t know how to change it to a many to many.
EX. Garment Table, Key Shopper_Phone_Number,…
I’ve created a recursive reltionship - Shopper_Phone_Number to Shopper_Phone_Number
If I need to explictedly define another table for the many to many relationship, what key field, above, would I use? Shopper_Phone_Number again?
I need some step by step directions please, thanks.
For n:m-Relationship you will need 3 tables. Something like “Table1”, “Table2” and “relTable1Table2”.
“Table1”.“ID1” is primarykey of “Table1”.
“Table2”.“ID2” is primarykey of “Table2”.
“relTable1Table2”.“ID1” and “relTable1Table2”.“ID2” are together primary key of “relTable1Table2”. And they are foreign keys to “Table1” and “Table2”.
Have a look at the simple example of first chapter of Base Guide: Chapter 1 Introducing Base.
Could you share your data structure and/or background logic with us? What is it you are trying to achieve?
E.g.: It may be that what you need is a join and not a relationship.
Distinction, roughly …
The reason for my comment: I cannot see a situation where a recursive relationship from a field in a table to the same field in the same table makes sense. In my mind there needs to be some condition/indirection. (Indirection is where the third table steps in, and also serves as a filtering condition so you can break circular recursion.)
https://forum.openoffice.org/en/forum/download/file.php?id=11250
The file has a one-to-many relation between animals and persons and a many-to-many relation between persons and tools. Please refer to the relations window (Tools>Relations). Everything is based on meaningless auto-IDs. No text nor date value establishes any relation. Text, dates, decimals, picture data are attributes of items that are identified by an ID number which is completely meaningless outside the relational context.
None of these auto-IDs is visible in the user interface (forms and reports). In Base forms, the IDs are covered by listboxes. They are used in form/subform constructs without the need to show them anywhere on the form (refer to subform properties).
Using a phone “number” as primary key is a bad idea. Phone numbers change or disappear. A changing phone number ID needs to be updated in every foreign key. A removed phone number leaves orphaned foreign keys. With a meaningless ID, you can change the phone number when it changed in the real world, you can add comments to phone numbers (“not on Monday mornings”). You can mark a phone number as deprecated, so it is still visible in old records while being hidden in up-to-date context.
I understand M:N and also M:M V. the resolution is to have another table in the M:N instance to relsove the Many side ofthe relationships. The key field from each of the first 2 tables would be the fields defined this 3rd table.
Asfar as a reason to the Many to Many recursive reelationship, here is how/why I’m choosing this design option:
1 Shirt has many Jackets: The Shirt can be used with several different
Jackets. 1 Jacket has many Shirts: The Jacket can be matched with
many different Shirts.
And thank you for your input.
After some more thought, I came to the conclusion that one can’t have a Many to Many Recursive relationship! I retired from a 23-year career in IT in 2006, almost 20 years ago. So, I’m a little rusty, to say the least! ha
I have an idea for a Smart Phone application, so that’s what this is all about. In order to what I need to do code wise is that I need to do a Join, which I feel will work. Another respondent suggested this.
Thanks for your input.
Thank you for your excellant suggestion regarding the use of phone numbers as a key! I had never thought of that, obviously. Fortunately, I had already created a 2nd key field, using a random number generator, so I can use that as my primary key!
It’s really hard to think about everything, being a design team of only one, me. lol
As far as the first part of your mge regarding Many to Many relationships, I understand the concept, and I think that I need another table to resolve this; a “resolution” table. What I don’t understand is:
how depict a many to many recursive relationship in the ERD,
what fields from my Garment Table would the key field(s) need to be in the “resolution” table?
I know that the primary key of Garmaent table would be one of the two fields. And if there were a 2nd table involved then the key from that table would be the 2nd field defined in this “resolution” table.
But, since I only have one table, Garment, what is the the 2nd key field that should be defined in the “resolution” table?
I do not even know which type of database you are using. Base is not a database. It’s a tool to work with databases. Did you examine my sample database? Relations window? Table design? The usual database engines can generate Auto-IDs of type integer and long integer. They are simple, integer row numbers.