locked
Many to Many relation problem RRS feed

  • Question

  • I am creating a DB that works like this for a VB 2010 project:

    User enters Year, Make and Model in drop down comboboxes - then a combobox list of specific wiring diagram systems displays (eg. Engine performance, trans circuit, etc), then user will select from that list and the DB will bring up the wiring schematic in a PictureBox.

    Heres my problem, I have set up the following DB relationship (submitted by John), and the many to many relationship does not seem to join properly.

    One CarType (defined by a make, model, year, and possibly also trim line) will have many Drawings.
    Each Drawing may apply to zero, one, or several CarTypes.

    This requires just three tables: a table of CarTypes such as

    CarTypes
      CarTypeID (autonumber primary key)
      MakeID (Text, for the Vehicle Make)

      ModelID (Text, for the vehicle Model)
      ModelYear (Long Integer, e.g. 1994 for my Toyota Camry)

    I have all the records into the previous table, there are 13536 records representing that many vehicle makes and models between 1990 and 2011, it has a primary key.

    A table of Drawings:

    Drawings
      DrawingID (autonumber primary key)
      Title (text, e.g. "Engine performance circuit")
      Drawing (path and filename of the drawing)

    CarDrawings
      CarDrawingID (autonumber primary key)
      CarTypeID (Long Integer, link to CarTypes, which car has this drawing)
      DrawingID (Long Integer, link to Drawings, which drawing is this)

    Some drawings might apply to only one car type, so there'd only be one record for that car; other drawings might apply to multiple makes, models, and years (e.g. if Fords, Mercurys and Lincolns from 1964 through 1971 all used the same diagrams).

    I am having a problem relating the titles to the individual drawings and also at the same time need to relate the drawing itself back to other vehicles it would also cover (creating a many to many relation).

    I have enclosed the layout, and any help would be greatly appreciated, thanks.................Pete

    • Edited by Pete - Saturday, May 18, 2013 1:47 AM
    Saturday, May 18, 2013 1:46 AM

Answers

  • 1.  You should ensure that the CarTypeID and DrawingID columns in the JoinVehiclestoDrawings table are included in a unique index as these two columns are a candidate key of the table.  In fact there is no real advantage in having a surrogate CarDrawingID primary key in this table as it is not referenced by any other table.  The CarTypeID and DrawingID columns could simply be made the composite primary key.

    2.  The Vehicles table contains a transitive dependency so is not normalized to Third Normal Form (3NF).  The MakeID column in this table is determined by ModelID, so is transitively dependent on the key.  For a table to be normalized to 3NF all non-key columns must be determined solely by the whole of the table's key; 'The key, the whole key and nothing but the key, so help me Codd'.  A Models table will include a foreign key MakeID column referencing the key of a makes table, so the make of vehicle is available via the relationships.

    3.  I assume that there is a DrawingLocations table or similar whose key is referenced by the DawingLocation column in Drawings, with an enforced relationship between the tables.  This protects the integrity of the data as only valid DawingLocation values can then be entered in Drawings.

    4.  I'd recommend that either the Vehicles table be renamed CarTypes, or the CarTypeID columns be renamed VehicleID for consistency.  These sort of semantic considerations might seem trivial, but they do make it easier when reading and writing SQL statements or VBA code.   As it stands the natural assumption of someone reading an SQL statement such as:

    SELECT CarTypeID, Model
    FROM Vehicles INNER JOIN Models
    ON Vehicles.ModelID = Models.ModelID
    WHERE Model = "F-Type";

    would probably be that this would return the ID for the type (category) of vehicle, e.g. a sports car in the case of a Jaguar F-Type.

    Ken Sheridan, Stafford, England

    Saturday, May 18, 2013 12:43 PM
  • What looks suspicious to me is the relationship between Drawings and CarDrawings.

    First, I would rename CarDrawings to indicate that it is a joining table, perhaps JCarToVehicle

    Second, change the relationship between these two tables to a type 1.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    • Marked as answer by Pete - Saturday, May 18, 2013 4:26 AM
    Saturday, May 18, 2013 2:27 AM

All replies

  • What looks suspicious to me is the relationship between Drawings and CarDrawings.

    First, I would rename CarDrawings to indicate that it is a joining table, perhaps JCarToVehicle

    Second, change the relationship between these two tables to a type 1.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    • Marked as answer by Pete - Saturday, May 18, 2013 4:26 AM
    Saturday, May 18, 2013 2:27 AM
  • Sorry it took so long to get back to you. I will try that and let you know. I appreciate the quick response. Thanks..........Pete
    • Edited by Pete - Saturday, May 18, 2013 2:59 AM
    Saturday, May 18, 2013 2:59 AM
  • Thanks Peter, it now produces queries normally. I think that by the time I enter all the necessary data for the joining table, I might as well stick to a flat table for "Vehicles" with all the fields of the drawings table within it. I dont really see much gain by joining tables, other than duplicating titles and drawing location.

    Would you have any other suggestions for simplifying the DB, or how to make it more efficient?

    Heres what it is now.

    Thanks...................Pete B.

     
    • Edited by Pete - Saturday, May 18, 2013 4:29 AM
    Saturday, May 18, 2013 4:26 AM
  • 1.  You should ensure that the CarTypeID and DrawingID columns in the JoinVehiclestoDrawings table are included in a unique index as these two columns are a candidate key of the table.  In fact there is no real advantage in having a surrogate CarDrawingID primary key in this table as it is not referenced by any other table.  The CarTypeID and DrawingID columns could simply be made the composite primary key.

    2.  The Vehicles table contains a transitive dependency so is not normalized to Third Normal Form (3NF).  The MakeID column in this table is determined by ModelID, so is transitively dependent on the key.  For a table to be normalized to 3NF all non-key columns must be determined solely by the whole of the table's key; 'The key, the whole key and nothing but the key, so help me Codd'.  A Models table will include a foreign key MakeID column referencing the key of a makes table, so the make of vehicle is available via the relationships.

    3.  I assume that there is a DrawingLocations table or similar whose key is referenced by the DawingLocation column in Drawings, with an enforced relationship between the tables.  This protects the integrity of the data as only valid DawingLocation values can then be entered in Drawings.

    4.  I'd recommend that either the Vehicles table be renamed CarTypes, or the CarTypeID columns be renamed VehicleID for consistency.  These sort of semantic considerations might seem trivial, but they do make it easier when reading and writing SQL statements or VBA code.   As it stands the natural assumption of someone reading an SQL statement such as:

    SELECT CarTypeID, Model
    FROM Vehicles INNER JOIN Models
    ON Vehicles.ModelID = Models.ModelID
    WHERE Model = "F-Type";

    would probably be that this would return the ID for the type (category) of vehicle, e.g. a sports car in the case of a Jaguar F-Type.

    Ken Sheridan, Stafford, England

    Saturday, May 18, 2013 12:43 PM
  • Ken,

    Thankyou very much for your insight on this. I will use your suggestions as described and incorporate it into the DB. Yes, I know I messed up the "CarType" scenario for readability. I generally will make long titles so I know exactly what is going on - Especially in VB Code, I'm always making notes, otherwise you can get lost easily. I will do the same in DB work from now on - I'm not a pro, like you, so long descriptive names help me - why not use them, huh, it doesnt hurt anything.

    Again, thankyou for the thought out reply,

    Pete Bambenek, Minnesota, USA 


    • Edited by Pete - Sunday, May 19, 2013 3:52 AM
    Sunday, May 19, 2013 3:52 AM
  • Actually I'm not a pro by any stretch of the imagination.  Like you I just use Access as a tool in my work.

    I couldn't agree more about the use of descriptive object names, particularly for tables and columns.  I try and follow the convention of using plural or collective nouns for tables (as tables are sets) and single nouns for columns (as a column represents an attribute).  I always try and use terms as close as possible to real English words which represent the entity type in the case of a table, or the attribute in the case of a column.  Like most experienced developers one thing I never do, however, is include spaces or other special characters like the # sign in object names. Instead I use CamelCase.

    Ken Sheridan, Stafford, England

    Sunday, May 19, 2013 10:53 AM