If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
First db design - normalisation question
Hi,
This is my first attempt at designing a db from scratch, and it might take a while to explain so please bear with me! I'm using Access 2003 and trying to create a contacts db for our work project. So far in my design, I've got three tables: tblOutsideContacts tblOurStaff tblMeetings (which has records for each time someone from tblOurStaff meets with someone from tblOutsideContacts) Each person (from either tblOutsideContacts or tblOurStaff) can be involved with 1 Department (there are 8 depts). I want to report on: i) a list of activity in each Dept for last two weeks, including who attended the meetings ii) search by Dept to find related people iii) search for a Person and find their Dept(s) What is the best way of recording Departments on these three tables? Do I need a separate Department table? If so, what is the best way to link it to the existing tables? Apologies if I'm missing something simple, just can't get my head round it. Hope this makes sense, all help very gratefully received! Thanks, MB |
#2
|
|||
|
|||
First db design - normalisation question
In message , Marie B
writes Hi, This is my first attempt at designing a db from scratch, and it might take a while to explain so please bear with me! I'm using Access 2003 and trying to create a contacts db for our work project. So far in my design, I've got three tables: tblOutsideContacts tblOurStaff tblMeetings (which has records for each time someone from tblOurStaff meets with someone from tblOutsideContacts) If there is a many to many relationship between meetings and staff you need a linking table. You probably also need a linking table between contacts and meetings. Linking tables contain at least the fields that are the primary key of each of the tables they link. Each person (from either tblOutsideContacts or tblOurStaff) can be involved with 1 Department (there are 8 depts). I want to report on: Just what role does the department play? Do you just need to record the department that the staff members belong to, or is there some other relationship between meetings and departments. i) a list of activity in each Dept for last two weeks, including who attended the meetings ii) search by Dept to find related people iii) search for a Person and find their Dept(s) What is the best way of recording Departments on these three tables? Do I need a separate Department table? If so, what is the best way to link it to the existing tables? Apologies if I'm missing something simple, just can't get my head round it. Hope this makes sense, all help very gratefully received! Thanks, MB -- Bernard Peek |
#3
|
|||
|
|||
First db design - normalisation question
Marie
I usually recommend that someone in your situation sit down with paper and pencil and start drawing pictures ... First, make a box to represent one of the "thingies" about which you want to keep information. From your description, it sounds like Meetings is one of those thingies. Now make other boxes to represent other persons/places/things about which you want to keep information. Now put the kind of information into each box that fits there. For example, if you have an OutsideContact box, I'm guessing you'll have a LastName, a FirstName, and maybe an Email address -- these are attributes/features of an OutsideContact. If you find that you have the same kinds of information in two boxes, consider that maybe you only have ONE thingie, not two. In your description you mention OutsideContacts and OurStaff. I'll assume that both hold "people", and information about people (e.g., Name, contact info like email addresses, ...). If you are keeping identical types of information (or close) about both OutsideContacts and OurStaff, consider using a single table that holds "people". Whether or not a person is an OutsideContact or an OurStaff may change over time. In your situation, could you have someone who used to work for you leave the company and become an OutsideContact? ... or vice versa? Relational design and normalization are part art, part science. There's no one right way (OK, so some of the gurus insist that there is, but this is the real world!). Good luck! Feel free to post back with further questions, or come back and answer some you know the answer to! Regards Jeff Boyce Microsoft Office/Access MVP "Marie B" wrote in message ... Hi, This is my first attempt at designing a db from scratch, and it might take a while to explain so please bear with me! I'm using Access 2003 and trying to create a contacts db for our work project. So far in my design, I've got three tables: tblOutsideContacts tblOurStaff tblMeetings (which has records for each time someone from tblOurStaff meets with someone from tblOutsideContacts) Each person (from either tblOutsideContacts or tblOurStaff) can be involved with 1 Department (there are 8 depts). I want to report on: i) a list of activity in each Dept for last two weeks, including who attended the meetings ii) search by Dept to find related people iii) search for a Person and find their Dept(s) What is the best way of recording Departments on these three tables? Do I need a separate Department table? If so, what is the best way to link it to the existing tables? Apologies if I'm missing something simple, just can't get my head round it. Hope this makes sense, all help very gratefully received! Thanks, MB |
#4
|
|||
|
|||
First db design - normalisation question
Hi Bernard,
Thanks for the info - I'll look into linking tables. Much obliged. Marie "Bernard Peek" wrote: In message , Marie B writes Hi, This is my first attempt at designing a db from scratch, and it might take a while to explain so please bear with me! I'm using Access 2003 and trying to create a contacts db for our work project. So far in my design, I've got three tables: tblOutsideContacts tblOurStaff tblMeetings (which has records for each time someone from tblOurStaff meets with someone from tblOutsideContacts) If there is a many to many relationship between meetings and staff you need a linking table. You probably also need a linking table between contacts and meetings. Linking tables contain at least the fields that are the primary key of each of the tables they link. Each person (from either tblOutsideContacts or tblOurStaff) can be involved with 1 Department (there are 8 depts). I want to report on: Just what role does the department play? Do you just need to record the department that the staff members belong to, or is there some other relationship between meetings and departments. i) a list of activity in each Dept for last two weeks, including who attended the meetings ii) search by Dept to find related people iii) search for a Person and find their Dept(s) What is the best way of recording Departments on these three tables? Do I need a separate Department table? If so, what is the best way to link it to the existing tables? Apologies if I'm missing something simple, just can't get my head round it. Hope this makes sense, all help very gratefully received! Thanks, MB -- Bernard Peek |
#5
|
|||
|
|||
First db design - normalisation question
Hi Jeff,
Many thanks for your informative and welcoming reply - will take your advice and think about whether I need two tables for contacts, or can put them into one...I may have been a little over-enthusiastic on normalisation after reading a couple of articles! No doubt I'll be back on these boards soon enough Regards, Anna "Jeff Boyce" wrote: Marie I usually recommend that someone in your situation sit down with paper and pencil and start drawing pictures ... First, make a box to represent one of the "thingies" about which you want to keep information. From your description, it sounds like Meetings is one of those thingies. Now make other boxes to represent other persons/places/things about which you want to keep information. Now put the kind of information into each box that fits there. For example, if you have an OutsideContact box, I'm guessing you'll have a LastName, a FirstName, and maybe an Email address -- these are attributes/features of an OutsideContact. If you find that you have the same kinds of information in two boxes, consider that maybe you only have ONE thingie, not two. In your description you mention OutsideContacts and OurStaff. I'll assume that both hold "people", and information about people (e.g., Name, contact info like email addresses, ...). If you are keeping identical types of information (or close) about both OutsideContacts and OurStaff, consider using a single table that holds "people". Whether or not a person is an OutsideContact or an OurStaff may change over time. In your situation, could you have someone who used to work for you leave the company and become an OutsideContact? ... or vice versa? Relational design and normalization are part art, part science. There's no one right way (OK, so some of the gurus insist that there is, but this is the real world!). Good luck! Feel free to post back with further questions, or come back and answer some you know the answer to! Regards Jeff Boyce Microsoft Office/Access MVP "Marie B" wrote in message ... Hi, This is my first attempt at designing a db from scratch, and it might take a while to explain so please bear with me! I'm using Access 2003 and trying to create a contacts db for our work project. So far in my design, I've got three tables: tblOutsideContacts tblOurStaff tblMeetings (which has records for each time someone from tblOurStaff meets with someone from tblOutsideContacts) Each person (from either tblOutsideContacts or tblOurStaff) can be involved with 1 Department (there are 8 depts). I want to report on: i) a list of activity in each Dept for last two weeks, including who attended the meetings ii) search by Dept to find related people iii) search for a Person and find their Dept(s) What is the best way of recording Departments on these three tables? Do I need a separate Department table? If so, what is the best way to link it to the existing tables? Apologies if I'm missing something simple, just can't get my head round it. Hope this makes sense, all help very gratefully received! Thanks, MB |
#6
|
|||
|
|||
First db design - normalisation question
Marie
That's why folks hang out here...! Regards Jeff Boyce Microsoft Office/Access MVP "Marie B" wrote in message ... Hi Jeff, Many thanks for your informative and welcoming reply - will take your advice and think about whether I need two tables for contacts, or can put them into one...I may have been a little over-enthusiastic on normalisation after reading a couple of articles! No doubt I'll be back on these boards soon enough Regards, Anna "Jeff Boyce" wrote: Marie I usually recommend that someone in your situation sit down with paper and pencil and start drawing pictures ... First, make a box to represent one of the "thingies" about which you want to keep information. From your description, it sounds like Meetings is one of those thingies. Now make other boxes to represent other persons/places/things about which you want to keep information. Now put the kind of information into each box that fits there. For example, if you have an OutsideContact box, I'm guessing you'll have a LastName, a FirstName, and maybe an Email address -- these are attributes/features of an OutsideContact. If you find that you have the same kinds of information in two boxes, consider that maybe you only have ONE thingie, not two. In your description you mention OutsideContacts and OurStaff. I'll assume that both hold "people", and information about people (e.g., Name, contact info like addresses, ...). If you are keeping identical types of information (or close) about both OutsideContacts and OurStaff, consider using a single table that holds "people". Whether or not a person is an OutsideContact or an OurStaff may change over time. In your situation, could you have someone who used to work for you leave the company and become an OutsideContact? ... or vice versa? Relational design and normalization are part art, part science. There's no one right way (OK, so some of the gurus insist that there is, but this is the real world!). Good luck! Feel free to post back with further questions, or come back and answer some you know the answer to! Regards Jeff Boyce Microsoft Office/Access MVP "Marie B" wrote in message ... Hi, This is my first attempt at designing a db from scratch, and it might take a while to explain so please bear with me! I'm using Access 2003 and trying to create a contacts db for our work project. So far in my design, I've got three tables: tblOutsideContacts tblOurStaff tblMeetings (which has records for each time someone from tblOurStaff meets with someone from tblOutsideContacts) Each person (from either tblOutsideContacts or tblOurStaff) can be involved with 1 Department (there are 8 depts). I want to report on: i) a list of activity in each Dept for last two weeks, including who attended the meetings ii) search by Dept to find related people iii) search for a Person and find their Dept(s) What is the best way of recording Departments on these three tables? Do I need a separate Department table? If so, what is the best way to link it to the existing tables? Apologies if I'm missing something simple, just can't get my head round it. Hope this makes sense, all help very gratefully received! Thanks, MB |
Thread Tools | |
Display Modes | |
|
|