Results 1 to 6 of 6
  1. #1
    Cholomanchuten is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    4

    Question Database challenge: can you find the answer?

    Hello. I am trying to design the following database, but i never had a table that referred to itself.



    I have a table with several articles. But some articles of this table are composed by other articles of this same table. Let me give you an example.

    Article
    CAR
    LIGHTS
    DOOR

    So, i want to make a relationship where CAR has LIGHTS and DOOR, but these are elements from the same table. ¿How can i do this?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may wish to research self-join, but I would suggest you look at the first few topics at this site related to Normal forms, normalization and entity relationship diagramming.
    http://www.rogersaccesslibrary.com/forum/topic238.html

    As far as the challenge goes, you might want to show us what you have tried.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    it also relies on how far your roots go

    You could have a table that was.

    PartID PartName ParentPartID
    1 Car
    2 Door 1
    3 Window 2

    where you define which parts are subsidiaries of which main parts.

  4. #4
    Cholomanchuten is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    4
    Yes, the problem is that a Car can have many parts, but a door is a component for many other registries in the same table. For example, if I had another record called truck, the "ParentPartID" wouldn't work because the door would apply to both car and truck...basically I want a many to many relationship within the same table.

  5. #5
    Cholomanchuten is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    4
    Also, you could help me think outside the box. I don't have much experience in table diagram design, but maybe this could be parted into many tables, i guess...

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do yourself a great favor - draw a picture of what the "pieces are" and "how they fit together". You know your environment and we don't. So you are the best source to create the initial picture/diagram. We can advise, answer questions, and suggest options, BUT only when we have a specific question/example to work with. Put your things into your context and then ask specific questions.

    You will learn more about tables and relationships by going through this process than you think. Take a good look at the link I gave you earlier -- especially the ER Diagramming.

    What you learn will be applicable to all table design and database structures.
    Good luck.

    The subject underlying your post is
    Bill of Materials or Hierarchies.

    There is info at this site
    http://www.tdan.com/view-special-features/5400/ but it may be too specific at this time. It is well worth reading if only to get some idea of the scope of the issue.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-19-2011, 07:17 PM
  2. Access can't find my database
    By Pandora in forum Access
    Replies: 10
    Last Post: 07-11-2011, 05:21 PM
  3. Anyone fancy a challenge????!!!!!!
    By gregh in forum Database Design
    Replies: 1
    Last Post: 03-14-2011, 05:36 PM
  4. Replies: 1
    Last Post: 02-17-2011, 10:57 PM
  5. Replies: 13
    Last Post: 02-08-2011, 11:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums