Results 1 to 3 of 3

Thread: Relationships

  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an Orders database I am developing. A customer can order many parts. Two types of parts are used. Part A and B. An order can contain many of either. However, Part B contains many of Part A. Part A cannot contain any Part B.

    I'm stumped as to how to relate the tables. I can make a many to many so one Part A can be used in many Part B, and one Part B can contain may Part A. However, I'm not sure how to create a relationship so an order can contain a Part B or a Part A.

    I hope this makes sense. I've attached a screen shot of my relationships without the missing join line to the OrderDetail table.

    Thanks in advance
    Attached Images Attached Images

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Your screen shot does not include a Parts Table unless it is called something else.

    To deal with Part Bs containing many Part As I would have a many-to-many relationship between the Parts Table and itself.
    [attachment=83328:PartsRelationships.gif]

    In tblPartsComponents PartIDA would contain the PartID for a type A part, PartIDB would hold the PartID for a Type B part, and the quantity field would say how many of the As go into a B.
    Attached Images Attached Images
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks. I sometimes run out of ideas - never thought of having a table used twice. I'll give it a try...


    (My screen shot has different table names, but your suggestions is fine.)

Posting Permissions

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