Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2015
    Posts
    15

    Unanswered: Many-to-Many Relationships from a Table to Itself (or Another Solution)

    Hello

    Please, i'd like to know if it's possible to create a many-to-many relationship from a table to itself in Microsoft Access.

    I need to create a Database to store Thoerems, Proofs, Definitions, Examples, Exercises, etc. All these kinds of objects would be in the same table. I think the relationship is many-to-many, since, for example, each theorem may use some different thorems and definitions, but also may be used by some other theorems or exercises.

    My intention is to get some kind of "tree view" in the end, with say 2 levels of depth, but i know this is another topic for a another post.

    I have no experience in SQL programming. I have learned very little of Python and C and that's my only experience with code. So i would prefer to avoid coding. If you think i could benefit a lot from learning SQL, then i will start studying it right now just to build this Database.

    Thank you very much for your atention and for wiling to help!

    Regards,
    Marco

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    u need a AT less 3 tables


    [main] table
    mainID PK
    bla
    bla
    bla
    ....

    [product] table
    productID PK
    bla
    bla
    ...
    ...

    now many-to-many table


    [many]
    manyID PK (just to keep table Happy)
    mainID
    productID
    ...
    ...
    ...

    this way you can have many mainID and or many productID
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2015
    Posts
    15
    Thank you very much for the reply!

    The problem is not to create any Many-to-Many relationship, but one from the table with itself. Anyway, i think i have found a solution. I'm testing it right now, just to be sure it works.

    Regards,
    Marco

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Are these the same entity type, and therefore suitable for the same table?
    Some of ig may be terminology but I dont think examples and exercises neccesarily fit with theorums, proofs and definitions.

    To me an example is a completed exercise
    Likewise a proof is a completed exercise of a definition.
    Are therorums and definitions essentially the same entity?

    So at present it looks like you need a minimum of 3 tables to me:-
    Theorums and definition
    Which may have one or more exercises
    Which in turn may have one more examples and or proofs


    If you are coming from a OOP approach dont be tempted to use an OOP approach to db design.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2015
    Posts
    15
    Hi,

    Thank you fvery much or the reply!

    Yes, i think you are right. I think that Definitions and Theorems together could be trated as one entity (of "enunciations"), while proofs, exercises and examples together could be trated as another entitiy (of arguments). I will think about that. Anyway, first i'm trying to solve the problem considering that they are all suitable for the sabe table.

    Bsically, i'm trying to think that all these objetcs relate to each other in this way: A uses B. So all the relations are of this "quality". So the table relates to itself many times in a situation like A uses F, that uses C, etc...

    At this moment, i think i found a solution that works for me, but, of course, i'm still trying to improve it. These are the tables and relationships. OBJECT always "uses" OBJECT1.

    AREA (the subject), OBJECT (with a foreign key to Area), RELATIONSHIP (it's a junction table, with a foreign key to Obejct and to Object1), OBJECT1.

    Regards,
    Marco

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when thinking about (relational) database schema (design) I oftne find it helps to think of xxx has a yyy, or bbb must have a aaaa

    eg:-
    customers must have one or more addresses
    orders must have a customer
    an order detail line must have an order number AND a product

    theorums may have many exercises
    a therorum many have one or more proofs

    when desiging entities don't be tempted to roll up different funstion requirements into a single entity. an entity should sit by itself. it shouldn't bve coerced to fit more than one requiremment. the basic principle behind normalisation is to remove repeating groups of information. but that doenst' mean you shoudl stuff several different things into one entity becuase OOP programming suggests yoiu should. the EAV model is often selected by people from an OOP background, and is a nominally attractive propostion. however in relational db's its invariably a bastardisation that will bring a design to its knees in a live db environment

    if two entities are simialr but not the smae then consider usign the sub/supertype model
    have a parent entity that stores what is common to all 'child' entities (sonfusingly the supertypes, and an entity for each supertype that stores data pertinent to that entity.. https://msdn.microsoft.com/en-us/library/cc505839.aspx
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2015
    Posts
    15
    Yes, you are making me re-think everything. I'm trying to work on a better model. I think i lack not only a better understanding of Databases (mine is pathetic), but also a better understanding of Mathematics itself. A deep discussion of what are the objects and how they relate to themselves is very interesting, but i actually need to have some kind of a "temporary" solution that works fine for my needs. Then i keep thinking about that to buil a better Database in the future.

    You mentioned "live environments", for example. This is a Database for personal use only. Althought it doesn't justify bad design, i think that it means i should not worry too much about things beyond my personal needs.

    You said:
    theorums may have many exercises
    a therorum many have one or more proofs

    I say:
    I don't like to think that Theorems "have" many exercises, but i think i see your point. I think you are putting Theorems or other smaller results in the "center of the action" (sorry for my ignorance), while other entities would be related to it. I also know that Theorems have more than one proof, but i also want to see other kinds of relationhips, that are even more important for my personal use.

    The relationships are essential for my needs. What really interests me is the ability to quickly get a kind of a "genealogy" of an argument. This is the use i want ot get from teh Database. Example: An argument uses another argument, refers to 2 other theorems or other results, uses 2 properties, and 3 definitions. Then each of these "used" objects "use" others.

    Except for Definitions and Axioms, maybe i should think of all the other objetcs as something that have "statements" and "arguments". So:

    DEFINITIONS have STATEMENTS that will REFER TO other DEFINITIONS.
    THEOREMS and other small results have STATEMENTS that will REFER TO other THEOREMS and DEFINITIONS, but also have ARGUMENTS (the proofs), that will REFER TO other objects.
    PROOF EXERCISES would be like THEOREMS and other small results.
    etc...

    The problem is that the relationships get too much complex for me to think about tables, queries, etc, in Access. So that's why i end up thinking that i should put all of them in the same table, at least while i still don't know Databases or even Mathematics as i should.

    Do you think that Graph Databses like Neo4j might help or would they be just a "quick fix" for not really learning about Relational Databases? I mean: is it apropriate or even possible to actually do what i want to do with a Relational Database?

    Then you VERY MUCH for the atention and help!

    Marco

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Withiout understanding what you are trying to acheieve then you will only get generic respoinses.

    I dont'[ understand what you mean by 'Thoerems, Proofs, Definitions, Examples, Exercises, etc'. its your application, its your data model, its your approach. But the very fact that you are giving a different name to (at least) 5 categories suggests 5 entitites, one for each. if there are multiple proofs for a theorum, then that may suggest another intersection table to model a many to many relationship.... but that would only apply if say the same proof would apply to more than one therorum

    taking a generic view, and it must be becuase we don't know the details of what you are doing.

    say you could have a many to many relationship between examples AND exercises, then you'd need 3 tables to model that in a conventional relational db. (the two 'parent' tables and the intersection table that maps one to the other, and contains any other pertinent data to that intersection.

    however this isnt' really a forum to teach such things, at least not in the Access forum of dBF....
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2015
    Posts
    15
    Yes, i understand what you mean. I understand your suggestions about tables and relationships. Maybe my problem is how to define the Mathematics objects and their relations. And also i have problems to imagine how to querry the databse to get what i want. But yes, it's too generic. So i will work on that in the next days. I will came back to the forum if i get stuck in something much more specific.

    Thank you very much for the help!

    Regards,
    Marco

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •