Help needed with table design/layout for newbie
in [Tables]
|
Prev: Confused over new db
Next: Core Competency Assessment
From: jase118 on 4 Jun 2010 17:47 Jörn Bosse wrote: >When you setup relations, the fields need the same type: both integer >values. As it seems, your tblSessionMembers is the table to create the >m:n-relationship between members and sessions. Thats why you have to put >your yes/noe-field into this table. If you realise it that way, you can >directly see who has paid by selecting the session, or which session a >user has already paid by selecting the user. >But your entitity-relationship-model seems to be fine at all. > >How to create a m:n in your case. Open the Relation dialog and add the >tables: tblSessionMembers, tblmembers, tblSession. >Then creat a relationship using drag and drop. Drag from the Primkey and >drop it on the foreignkey. > >Regards >Jörn. > >Am 04.06.2010 22:49, schrieb jase118: >>>> Hi, i have just joined the forum and have built a few DB's in the past but >>>> found i have forgotten almost everything! >[quoted text clipped - 76 lines] >> I am not sure how to setup a foreign key, went into relationships and setup a >> 1 to many link, is this correct? Thanks for the advise so far, I have put payment and joining instructions under session members: tblSessionMembers -pkSessionMemberID primary key, autonumber -fkSessionID foreign key to tblSessions -fkMemberID foreign key to tblMembers -logPaid yes/no -logInstructionsSent yes/no
From: Jörn Bosse on 4 Jun 2010 18:44 Yep that looks fine. Remember if you need a field in comination with a user and sessions, you have to put it in that table. Any more questions? Jörn Am 04.06.2010 23:47, schrieb jase118: > Jörn Bosse wrote: >> When you setup relations, the fields need the same type: both integer >> values. As it seems, your tblSessionMembers is the table to create the >> m:n-relationship between members and sessions. Thats why you have to put >> your yes/noe-field into this table. If you realise it that way, you can >> directly see who has paid by selecting the session, or which session a >> user has already paid by selecting the user. >> But your entitity-relationship-model seems to be fine at all. >> >> How to create a m:n in your case. Open the Relation dialog and add the >> tables: tblSessionMembers, tblmembers, tblSession. >> Then creat a relationship using drag and drop. Drag from the Primkey and >> drop it on the foreignkey. >> >> Regards >> Jörn. >> >> Am 04.06.2010 22:49, schrieb jase118: >>>>> Hi, i have just joined the forum and have built a few DB's in the past but >>>>> found i have forgotten almost everything! >> [quoted text clipped - 76 lines] >>> I am not sure how to setup a foreign key, went into relationships and setup a >>> 1 to many link, is this correct? > Thanks for the advise so far, > I have put payment and joining instructions under session members: > > tblSessionMembers > -pkSessionMemberID primary key, autonumber > -fkSessionID foreign key to tblSessions > -fkMemberID foreign key to tblMembers > -logPaid yes/no > -logInstructionsSent yes/no >
From: jase118 via AccessMonster.com on 5 Jun 2010 05:13 Jörn Bosse wrote: >Yep that looks fine. Remember if you need a field in comination with a >user and sessions, you have to put it in that table. >Any more questions? > >Jörn > >Am 04.06.2010 23:47, schrieb jase118: >>> When you setup relations, the fields need the same type: both integer >>> values. As it seems, your tblSessionMembers is the table to create the >[quoted text clipped - 27 lines] >> -logPaid yes/no >> -logInstructionsSent yes/no This is a link to a picture of my tables and relationships, i have attempted to normalize them and insure the relevant data is in the correct place, let me know what you think. http://www.dbforums.com/microsoft-access/1657146-help-needed-table-design-layout-newbie.html#post6463536 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201006/1
From: Jörn Bosse on 5 Jun 2010 07:35 Hi, thats looks fine. But i would add a location to the sessions table and for the members maybe a date of birth. But the rest is really ok. You did a gread job so far. Jörn Am 05.06.2010 11:13, schrieb jase118 via AccessMonster.com: > Jörn Bosse wrote: >> Yep that looks fine. Remember if you need a field in comination with a >> user and sessions, you have to put it in that table. >> Any more questions? >> >> Jörn >> >> Am 04.06.2010 23:47, schrieb jase118: >>>> When you setup relations, the fields need the same type: both integer >>>> values. As it seems, your tblSessionMembers is the table to create the >> [quoted text clipped - 27 lines] >>> -logPaid yes/no >>> -logInstructionsSent yes/no > > This is a link to a picture of my tables and relationships, i have attempted > to normalize them and insure the relevant data is in the correct place, let > me know what you think. > > http://www.dbforums.com/microsoft-access/1657146-help-needed-table-design-layout-newbie.html#post6463536 > >
From: jase118 via AccessMonster.com on 5 Jun 2010 14:55
Jörn Bosse wrote: >Hi, > >thats looks fine. But i would add a location to the sessions table and >for the members maybe a date of birth. But the rest is really ok. >You did a gread job so far. > >Jörn > >Am 05.06.2010 11:13, schrieb jase118 via AccessMonster.com: >>> Yep that looks fine. Remember if you need a field in comination with a >>> user and sessions, you have to put it in that table. >[quoted text clipped - 14 lines] >> >> http://www.dbforums.com/microsoft-access/1657146-help-needed-table-design-layout-newbie.html#post6463536 Thanks, i am moving onto the next step, queries! be ready for many more posts and thanks again -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201006/1 |