database design (2007)

Topic

New Reply

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?

Reply | Quote

Replies

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 for an example of how to handle data entry for a many-to-many relationship with a main form and subform.

Reply | Quote

Hans,

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

Yes, that looks OK.

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

Reply | Quote

Hans,

Many thanks for the tutorial.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote