Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2008
    Posts
    10

    Unanswered: Purpose of redundant tables in design view & relationship window

    This is a concept understanding question/Post.

    BTW: (I have never typed one character of useful VBA code other than snippets off of web pages just to satisfy wild curiosities.)

    Hence, there is no need to ask questions about why the company is doing this or that.

    Database Tools | Relationships design
    I have noticed that I can make a second copy of a table in the Relationships window/tab by repetitively selecting the same table from the Show Table dialog box.

    However, those redundant tables do not show up in the queries' design show table pane.

    These tables can have their own relationship types set up with other tables. Why?

    Create | Query design view

    In the show table pane, I can have the same redundant tables show up AND those tables appear in the grid's Table: row.

    Even though, I cannot think of a need for it off the top of my head, I can see the possibility of querying the original table's join and then merging that dynaset/query return with the original table's copy where the copy of the original table is joined with a third table or the same table used by THE original table but the copy is looking at a different field or the same field but the join type is different.

    WOW! I really can follow what I just typed in the previous paragraph, but cannot think of a practical example. Consequently, I just scratch my head and say, "OK".

    However, I still would love to read an example of how the redundant table from the Relationships window is used.

    I thank you all very much in advance.
    This forum ROCKS!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not sure I understand what you mean about 'redundant tables', however I have seen something similar when defining two realtionships between the same two tables. the graphical tool can't handle this without having two instance of one table and one of the other. it may be possibel to add multiple instances of the same table, but thats an artificat of the realtionships tool. I think its clever enough to resolve these automatically when it comens to writing the SQL behind the scenes to defien the relationships.

    to find out if this is true delete all the objects, then add all the tabkels back again and see what happens. just in case deleting the objects deletes the realtionships do this on a backup of the db not you live or development model
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The only example that I can think of is in a database that has not been properly normalized. In some real world situations (when normalizing may mean several weeks or months of reprogramming) it may be necessary to add two fields that hold the same information. For example, two CompanyIDs. If the CompanyIDs relate back to a Company table and you have CompanyID1 and CompanyID2 you would need to add two instances of the Company table to show the relationship between the two tables.

    I admit that I have done this in the past (as a last resort or in a pinch) but it is definitely ideal.

  4. #4
    Join Date
    Jul 2008
    Posts
    10

    please, see attached, i need to be sure we are on same table :)

    I thank you all for your time. I have attached an instance of what I am talking about. You will see it helpless in the attached Word file.
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as said before

    that is the way the GUI represents two tables with two different RI links. its not an issue, as the code behind the scenes correctly defines the relationships. I guess its how the original designers of this screen saw the best way of doing that task. these days most tools would use an intellionsense type object when you hover over the RI link in question.

    if it offends you that much amke the two tblProducts the same size, place tblProoducts over tblproducts_1 so that tblProducts_1 is completly covered and hey presto it looks like there is only one table there.

    BTW (1) Im very suspicious about productID1,Id2,ID3 in data model terms, that sort of design usually indicates a flawed data model

    BTW (2) try no to use spaces in your object names, you cna get away with it in Access/JET but its not smart or clever, instead use CaMeLcAsE or and underscore

    eg
    ProductID or Product_UD
    ProductLevelID or Product_Level_ID

    BTW(3) don't store quantity on hand inside the table, it should be possible to generate the QTY on hand by doing a SQL sum on the transactions on that product. derived values such as this are a no no, especially in a multi user environment

    BTW(4) consider using full relational links check the 'enforce referential integrity' option, and consider if you want to enforce cascade updates and/or cascade deletes
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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