Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    database design (2007)

    Presently I have a db with tables for Members, Events and Momentos. Members can only attend 1 event and get 1 Momento (bad idea). So, I need to make some changes.

    tblEvents and tblMomentos each have a respective ID and Name (i.e. EventID, EventName). If Members are to be able to go to multiple Events and/or get multiple Momentos, seems that there will be two more tables needed: tblEventsAttending and tbl MomentosOrdered. Each table will have multiple MemberID's linked to an EventID and Event Name for the tblEventsAttending or linked to a MomentoID and MomentoName for the tblMomentosOrdered.

    Is this the best approach to design the db?
    Thanks

    Kim

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: database design (2007)

    Yes, junction tables are the way to implement a many-to-many relationship. The tblEventsAttending table will contain fields MemberID and EventID, that together (combined) will form the promary key of this table, plus if necessary other fields that are necessary for a member - event combination. If a member attends 5 events, there will be 5 records with his/her MemberID and different EventIDs. If an event is attended by 24 members, there will be 24 records with that EventID and different MemberIDs.

    Note: if MemberID is an AutoNumber field in tblMembers, it should *not* be an AutoNumber field in tblEventsAttending, but a Number field (Long Integer). Similar for EventID.

    See <post:=364,203>post 364,203</post:> for an example of how to handle data entry for a many-to-many relationship with a main form and subform.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: database design (2007)

    Hans,

    Thanks for the response. Please help me with the comment 'that together (combined) will form the primary key'. I do not understand this.
    Thanks

    Kim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: database design (2007)

    In many tables, the primary key is on a single field. This field has to be unique - it may not contain duplicate values.
    In a junction table for a many-to-many relationship, you don't want either of the ID fields to be unique by itself. The combinations of the two ID fields should be unique. This is accomplished by creating a primary key that consists of the two ID fields. The simplest way to create such a composite primary key is to select both ID fields in the table design window, then click the Primary Key button.
    The screenshot below shows what a composite primary key looks like.
    Attached Images Attached Images
    • File Type: png x.png (914 Bytes, 1 views)

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: database design (2007)

    Thanks for the education. I did exactly as you suggested right after I posted. But now I understand why it is done. Well, maybe almost.

    I now have on to many relationships tblEvents(eventID) to tblEventAttending(EventID); tblEventAttending (MembersID) and tblMembers (ID). Also one to many relationships tblMomentos (MomentoID) to tblMomentoOrdered (MomentoID); tblMomentoOrdered (MembersID) to tblMembers (ID) where tblEventAttending and tblMomentoOrdered are the intermediary tables.

    Is this look correct?
    Thanks

    Kim

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: database design (2007)

    Yes, that looks OK. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    (The "many" side of each relationship should be on the side of the intermediary table)

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: database design (2007)

    Hans,

    Many thanks for the tutorial.
    Thanks

    Kim

  8. #8
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: database design (2007)

    Works like a charm. Now, however, I need to get the data updated/called in VB6. Pass MemberID with this where GiftOrdered table is the intermediary between tables Members and Gifts:

    sSql = "SELECT Gifts.Gift, Gifts.Cost, GiftOrdered.MemberID FROM Members" & _
    " INNER JOIN (Gifts INNER JOIN GiftOrdered ON Gifts.GiftID=GiftOrdered.GiftID)" & _
    " ON Members.ID=GiftOrdered.MemberID" & _
    " WHERE GiftOrdered.MemberID = " & iID
    Set rs = cData.Execute(sSql)

    I get a BOF/EOF error msg. Any ideas are needed and appreciated.
    Thanks

    Kim

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: database design (2007)

    I don't have VB6, so I hope someone else will be able to help you with this.

  10. #10
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: database design (2007)

    After working with this one to many relationship, I am beginning to think that maybe it is not constructed properly therefore the results I am expecting are not accurate. I have attached a jpg of the way I constructed the relationships. Any/all comments are welcome.
    Attached Images Attached Images
    Thanks

    Kim

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: database design (2007)

    It depends on what you want to accomplish. Your screenshot shows a many-to-many relationship between Members and Gifts - one member can order several gifts, and several members can order the same gift, but each member can order a specific gift only once. Is that your intention? If a member must be able to order the same gift several times, you must either add another field (such as a date field) to the primary key of the intermediate table, or use another primary key altogether (such as an AutoNumber field). If you want a member to be able to order several gifts at once, you need another table: Orders, with OrderID (AutoNumber) as primary key and MemberID as foreign key to link the Orders table to the Members table. The GiftOrdered table would have a foreign key OrderID instead of MemberID.
    Does Access 2007 still come with the Northwind sample database? If so, take a look at it to see how the various tables are linked together there.

  12. #12
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: database design (2007)

    Thanks, Hans.

    Apparently, my intentions are not what I thought they should be. Yes, I want members to be able to order several gifts.

    I have the Northwinds.mdb and will take a look at it.
    Thanks

    Kim

Posting Permissions

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