Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2005
    Posts
    43

    Unanswered: ERD - would table referring to itself work?

    Greetings:

    I'm trying to create a database that tries to document the structure of another Access db. (I'm not trying to link the two or anything; they're truly separate databases. However, the first database has no documentation, and the documenter, while a start, doesn't give much of a map.) In my Entity Relationship Diagram of my "Metadata DB," I have one table called objNames, which gives the names of specific objects within the other database, e.g., a form called form1 is an objName. I have another table called linksToObjects, which I want to use to identify which objNames are related to other objNames. For example, if table1, table3, and table8 make up the query myQuery123, then i want to create an entry within linksToObjects to catalog this.

    My question is: in looking at my ERD, I have a many-to-many relationship between objNames - linksToObjects - objNames. Is this doable, or will Access not like this? I've attached a snapshot of this part of my ERD.

    If this is incorrect, does anyone have any advice with regard to the structure so I can accomplish this?
    Attached Thumbnails Attached Thumbnails erd_doesThisWork.jpg  
    Mos

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi
    Quote Originally Posted by get.mos
    My question is: in looking at my ERD, I have a many-to-many relationship between objNames - linksToObjects - objNames. Is this doable, or will Access not like this? I've attached a snapshot of this part of my ERD.

    If this is incorrect, does anyone have any advice with regard to the structure so I can accomplish this?
    I'll 'fess up - I really don't use jet much now but I think this will work just fine. The biggest problem I can see is that (for example) objects a and b might be related and appear in the table twice (just in different orders in the composite alternate key).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2005
    Posts
    43
    pf - i do appreciate your input. thank you. and i still welcome input from others...
    Last edited by get.mos; 07-20-07 at 21:00.
    Mos

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding a table linking back to itself you're talking about a recursive structure. It's a methodology taught in some schools and it's a pain in the #@@ to make queries out of it. I personally would stick with the straight-forward relational linking structure of one table to another table and avoid having a table link back to itself. It's extremely difficult to qc recursive structures for accuracy and (maybe it's just me) but they are not fun to work with (the least fun of anything I've worked with). A lot of game coding will use recursive structures (I don't why though but I think it's due to the speed of execution).

    If you plan on using a recursive structure, plan on writing code for just about everything as simple queries are almost impossible to return totals or data sets. Also plan on twice the development time (unless you've worked a lot with recursive structures and don't mind writing somewhat complex code for something which could normally be done with a simple relational table query.)
    Last edited by pkstormy; 07-24-07 at 17:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pkstormy
    It's extremely difficult to qc recursive structures for accuracy and (maybe it's just me) but they are not fun to work with
    paul, it's just you

    recursive structures are child's play, a walk in the park, a piece of cake, a cinch, a breeze, a snap

    see Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You must do a lot with them Rudy (or being a tad sarcastic ). I don't care for them much myself and only come across them once in a while. Can you tell me what the theory is behind constructing a db this way other than speed and making queries more difficult to work with?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I've developed a hatred for them after having to spend many hours reconstructing them to get simple totals. But like you said, it's probably just me. Good link of info though. I had a co-worker fresh out of school who was all excited about them and wanted to design everything using them.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I read your link again more thoroughly and I'm still not sold on them. I come across them in every 1 out of 30 or so databases.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, paul, there was supposed to be a smiley on my post, but when i went back to edit it, the darned internets failed again

    it doesn't seem to be dbforums only, i'm having trouble seeing a number of sites today -- is anyone else having this problem? maybe it's just my service provider...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I still can't tell if you're serious about them or not Rudy. (I need to see the expression on your face when you answer.) Things have been a tad too serious for me today with all the issues I've been dealing with.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think it depends on what the OP wants to get from the tables. But in any event - what alternative structure could handle the requirement?

    BTW - Rudy is not being funny - I know him well enough now to not need the smilies to get a read on him most of the time
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2005
    Posts
    43
    hey guys, thank you for the input! i hadn't checked in a while, as i was straddled with other stuff. but now that i'm back to it...

    so, i've not yet ever felt the need to create an erd w/a table referring back to itself, but maybe if i can give more detail, someone can suggest an alternative? (i guess i just don't see it, but, at the same time, i'm not sure this recursive structure will work?)

    an example of how i can see the need for this is:
    in my table objNames, there's a record for table123, which is a table object w/in another Access db (the db i'm trying to document). table123 is used in query query123, which is another record in objNames. query123 also uses table456. and my many-to-many table linksToObjects is what allows me to establish this relationship.

    from a SQL standpoint, this recursive structure thing is sound? i'm going to check out that link you gave, r937, and thank you for your input. my main concern is that the data will all come out. my end purpose of this database is just to be able to create a report that will show these relationships, as i've inherited an Access db w/o any documentation and that, over time, has been piecemealed into its current functionality.
    Mos

Posting Permissions

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