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 |
#21
|
|||
|
|||
Still Struggling...
I would echo what Bruce said and thank you for your kind reply. Here are
a few additional thoughts of my own. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). Happy to help. At least you want to learn and do things the right way. The posters we don't like are the ones that have already decided how they want to do things, even though it may be wrong, and expect someone here to explain to them how to do it the *wrong* way. Bothered by empty fields? Good. You should be. I know in a previous post I said something along the lines of "I might just live with a couple of empty fields in the Employees table", but I was just being lazy (shame on me!). Part of the goal of normalization is to eliminate redundancy and empty or "dead" fields. If an attribute doesn't apply to all employees, all the time, then it is an attribute of a sub-type, and therefore probably belongs in another table. Some other things to keep in mind for the future in the development cycle of your application; 1)Do not use "lookup fields" in your tables. This basically ampunts to a combo box, or list box, in a table. Combo/List boxes in forms are appropriate, but they should never be used in tables. See this link for more info; http://www.mvps.org/access/lookupfields.htm 2)Don't use spaces or other special characters in your table or field names. The same goes for queries, forms and reports (when you get to that point). Having spaces, etc. in the names will only cause you headaches later on. So instead of "Employees Table", it would more commonly be "tblEmployees". 3)Certain words are "reserved" in Access and should not be used as names of objects in your app. They are the names of different properties, etc. of the application, and if you use them as, for example, a field name it will cause problems. Two very common examples are Name and Date. See this link for a more complete list; http://support.microsoft.com/kb/286335 4)Most developers use some sort of "naming convention" to help keep track of things as the application grows in size. Everyone has some of their own variations on naming conventions, but some things are common. For example; Table names commonly start with tbl Queries start with qry (or something similar) Forms start with frm, Sub forms start with fsub, sfrm, etc. Reports start with rpt A text box (on a form or report) would start with txt A combo box (same as above) would start with cbo etc. 5)Continuing with the naming theme. When you get to the point where you start designing forms and reports, you will notice that when you add a control (text box, combo box, etc.) to a form/report, Access will give will give it a completely useless name like "Text52" or "Combo38". Do yourself a favor and name them something meaningful like "txtEmployeePhone" or "cboSelectASubject". Good luck and post back when you have more questions. -- _________ Sean Bailey "Aria" wrote: First, let me say,"WOW"! You both have given me *so much* information. Thank you! My comments are below (I'm combining posts. I didn't know how else to do this): Bruce M: You could have a Titles table and an Employees table, with a EmployeeTitle junction table. That may make some sense if several employees have the same title, but if a lot of titles are unique that approach could become awkward. My thinking is that there would be a main form based on tblEmployees, with a subform based on the junction table. Aria: Do I need a subform if the majority of the staff only hold one title? Yes, I still trying to get over having empty fields but I haven't made it this far so I don't know. The form I envision would have this info on the main form using a combobox for the titles. I am trying to think of every possible title that I can. I don't want any suprises here. Bruce M: If you would like to do something such as generate reports that list employees by title, the junction table is probably the best way to go (or the most flexible, at least). Aria: That is exactly what I want to do; grouped by title. I think I'll do the junction table. Bruce M: You may do well to start a new thread on this specific topic. I'm not sure I have the experience to advise you confidently that one choice is better than another in a particular situation. Aria: I know I'm struggling but sometimes that's o.k. (I can't believe I said *that*!) Let me qualify that remark by saying it depends on the hour. I know I need to learn how to figure out some of these issues myself. I have to try first. If I continue to have problems, I will of course follow your advice (you and Beetle). Bruce M: The thing is that this database is about keys, but once you have an Employee table you will probably use it again and again (as a linked table in other projects), so it is well to design it as carefully as can be done. Aria: Absolutely!!! You know our history here (some bad memories). Keys are problematic for us. I have always said that I don't mind putting in the hard work at the beginning so that it will seem effortless in the end. Well, here it is. I am willing to put in the effort but some days I am frustrated beyond belief. I had to add a phone table because there are a possiblity of 3 phone #s that a staff member may have aside from the room phone#, which I moved to tblLocations per our previous discussion. I think this is a 1:M relationship. Bruce M: One employee: several phones is one-to-many, but if a phone could have several people answering it, depending on the time of day, you may need a jPhonePerson unction table. StartTime and EndTime may be fields in the junction table for the room phones. Ideally this would be linked to scheduling, so that a class schedule would indicate when an employee could be reached at a particular room phone (or something like that), but that could become quite complex. Aria: Whew! Thank goodness I don't have to worry about that. A room phone could have several people answer it. This will depend on who is assigned to the room. The good thing is that they don't roam. All bets are off for the upcoming school year though. I've heard rumors. I should be getting a room assignment list from the Principal soon. We'll see.... Aria: We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. Bruce M: If so, you could just store the department in tblEmployees. However, be sure of this, as it is moredifficult to rearrange the database later. Aria: Ugh! Nooooooo...I was so sure! I checked over and over and over again and last night I finally found it...one person, two departments. Will this never end?! "Beetle" wrote: One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of information there. I had to really pay attention though and relate it to my situation. After reading it, I'm not as frustrated. Yes, there are 1:1 relationships here but it may be what I need. I do think that I have sub-types. Let me work with this some more before I let you both know which 1:1 relationships I have. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. Great...I think I have at least one situation where this is true. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions. Hey, perfect example! Hopefully this will help you. If you still have questions, as Bruce said it may be helpbul to start a new thread. He and I may be the only ones still following this thread. It does help. Thank you for that. I want to continue trying before I throw in the towel and ask for help. Comments for both of you: 1. You two are *great* teachers who always give me homework! There is such a wealth of information, instructions and suggestions that you put into your replies that I can't post back right away; I'm studying, highlighting and taking notes! 2. I've learned SO much from you (the collective you). I consider myself incredibly fortunate that you responded to my post. 3. I have varying degrees of frustration and confusion. I'm confused by some of my own table names (yeah, I know that doesn't even begin to make sense to you. I'll work on more logical naming). I'm also confusing myself by thinking to hard and long about some of these issues. I know the answer is right in front of me but I can't see it. 4. Like I said before, you both pan wide and then zoom in, incrementally, to get to the crux of a problem. I want to do that. This is starting to come into focus a little more now. I just have to keep reminding myself that problems I encounter now, will be be something I won't have to deal with later after everything is set. It's frustrating but I'm trying to keep it in perspective. 5. See #3. I'm going to have to take a step back. I have been working on this day and night (in between my day job). I have been thinking about database design in my sleep (I know, I've got it bad). 6. I have taken up so much of your time already. You have moved me forward by leaps and bounds. I understand that there are others who need help too. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). You have no idea how dejected I was when I first posted, but every question answered unraveled the knot just a little bit more. I'm going to take a couple of days and see if I can work out this employee table. When I return, I would like to get back to tblLocations, tblKeys, etc. I hope you will still be here, but if not, I completely understand. I just wanted you to know how grateful I am. I'm sure this is more than you *ever* wanted to know about locks, keys and school "issues". -- Aria W. "Beetle" wrote: The Employee section of the db seems to be expanding. I'm still struggling to work it out. As far as I can see, there seems to be some 1:1 relationships. As it stands now, there are at least 3 junction tables. One thing is clear though, no matter how I work this, there will be empty fields in some of the tables. I guess I *am* going to have to live with it. One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. ************************************************** * When it comes to people connected to the church in some way or other things get a little more complex, but not frighteningly so, as the principles involved are quite simple. All people share certain attribute types of course; we all have names, an address, a date of birth and so on. So there is an entity type People with these common attribute types, and a table can represent this attribute type. People with different roles may well have attribute types which are specific to their role, e.g. a pastor is likely to have attribute types which a member of his congregation would not have. If I can use an example from the C of E an attribute type for a C of E priest might be Date of Ordination. So the entity type Pastors is a sub-type of the entity type People. The way a sub-type is modelled in a relational data base is by means of a one-to-one relationship. In the case of People and its sub-type Pastors this would mean that there would be a table people with a numeric primary key PersonID (don't use names as a primary key, they can be duplicated), and a Pastors table also with a numeric primary key PersonID (you can call it PastorID if you wish, but I prefer to keep the column names the same). In the case of the Pastors table PersonID would also be a foreign key referencing the primary key of People. The people table would have columns for the common attributes like names and address etc., the Pastors table would have columns only for those attribute type specific to the Pastors entity type, e.g. Date of Ordination, but not the common attributes like names and address. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. When it comes to events connected with a church you need to think carefully about how these fit into the model. One thing you need to be careful of is deciding whether an event type is a sub-type of another type, or whether the type of an event is an attribute type of a single entity type. You might have a topmost Events entity type with attribute type such as Event Date, Location etc and a Baptisms sub-type with attribute types such as ChildID, FatherID, MotherID, all three referencing the People table. Another way of modelling it would be to have a single Events table with an attribute type EventType, in which a value 'Baptism' would be entered. The problem with the latter approach of course is that if you also have ChildID, FatherID, MotherID columns these won't be appropriate to other types of events in the same table. In this case therefore a type/sub-type model is probably the most appropriate. In the case of other entity types, however, the second approach might be more suitable. To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions. ************************************************** ** Hopefully this will help you. If you still have questions, as Bruce said it may be helpbul to start a new thread. He and I may be the only ones still following this thread. If you need to repost, it may be helpful if you include some more detailed information about your Employees table such as; 1) What different types of employees you need to track 2) Which attributes are common to *all* employees 3) Which attributes are specific to only certain employees. -- _________ Sean Bailey "Aria" wrote: I'd like to address this part of your post 1st, Bruce: It would probably be best to keep a single thread going. Beetle and I may end up saying the same thing, or slightly different things about the same topic, in the two branches of this thread. Since there is more information, including discussions of master keys and other matters, in the other thread, let's keep the discussion there. ...as you wish. I confess my ignorance here; all of this is new to me (Access and posting). Before I was referred here by someone in my district, I had never heard of a newsgroup/discussion group before. I had never posted anywhere at anytime. I didn't realize I was creating additional threads. I think I may have messed up this thread too. I'm just not sure how all of this is supposed to work. I read the Getting Started section and read a lot of posts before launching my own but... My apologies for any confusion I may have caused. Your tblLocks is heading in the right direction. Let me suggest LocationID rather than RoomID (in case the lock is to a building or a stadium or something), and tblLocations instead of tblRooms. You could have a field in tblLocations to identify the Location type (Room, Closet, etc.). Wing, Campus, etc. are attributes of the Location. If you have linked to tblLocations, all of the information in tblLocations is available (including Wing, etc.). No need to store such items in the Locks table. I think your suggestion is perfect and rectifies the limitations encountered with tblRooms. I have made the changes and am working on incorporating this into the db. Update- Upon further reflection, I have scrapped tblSubs. No one cared for that table except me.(smile) I thought it made sense, but I don't have any experience in this area so I defer to your judgement and suggestions. I am continuing to have problems with tblEmployees. It seemed so |
#22
|
|||
|
|||
Still Struggling...
As for control names, a field dragged onto a form or report from the field
list will give a text box the same name as the field, which can cause its own problems. Microsoft causes problems at times with its efforts to be helpful. While I agree in general about empty fields, there are reasonable exceptions to the rule. For instance, I don't think I would create a table for name suffixes such as Jr., Sr. etc. For reserved words the single best resource of which I am aware is he http://allenbrowne.com/Ap****ueBadWord.html "Beetle" wrote in message ... I would echo what Bruce said and thank you for your kind reply. Here are a few additional thoughts of my own. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). Happy to help. At least you want to learn and do things the right way. The posters we don't like are the ones that have already decided how they want to do things, even though it may be wrong, and expect someone here to explain to them how to do it the *wrong* way. Bothered by empty fields? Good. You should be. I know in a previous post I said something along the lines of "I might just live with a couple of empty fields in the Employees table", but I was just being lazy (shame on me!). Part of the goal of normalization is to eliminate redundancy and empty or "dead" fields. If an attribute doesn't apply to all employees, all the time, then it is an attribute of a sub-type, and therefore probably belongs in another table. Some other things to keep in mind for the future in the development cycle of your application; 1)Do not use "lookup fields" in your tables. This basically ampunts to a combo box, or list box, in a table. Combo/List boxes in forms are appropriate, but they should never be used in tables. See this link for more info; http://www.mvps.org/access/lookupfields.htm 2)Don't use spaces or other special characters in your table or field names. The same goes for queries, forms and reports (when you get to that point). Having spaces, etc. in the names will only cause you headaches later on. So instead of "Employees Table", it would more commonly be "tblEmployees". 3)Certain words are "reserved" in Access and should not be used as names of objects in your app. They are the names of different properties, etc. of the application, and if you use them as, for example, a field name it will cause problems. Two very common examples are Name and Date. See this link for a more complete list; http://support.microsoft.com/kb/286335 4)Most developers use some sort of "naming convention" to help keep track of things as the application grows in size. Everyone has some of their own variations on naming conventions, but some things are common. For example; Table names commonly start with tbl Queries start with qry (or something similar) Forms start with frm, Sub forms start with fsub, sfrm, etc. Reports start with rpt A text box (on a form or report) would start with txt A combo box (same as above) would start with cbo etc. 5)Continuing with the naming theme. When you get to the point where you start designing forms and reports, you will notice that when you add a control (text box, combo box, etc.) to a form/report, Access will give will give it a completely useless name like "Text52" or "Combo38". Do yourself a favor and name them something meaningful like "txtEmployeePhone" or "cboSelectASubject". Good luck and post back when you have more questions. -- _________ Sean Bailey "Aria" wrote: First, let me say,"WOW"! You both have given me *so much* information. Thank you! My comments are below (I'm combining posts. I didn't know how else to do this): Bruce M: You could have a Titles table and an Employees table, with a EmployeeTitle junction table. That may make some sense if several employees have the same title, but if a lot of titles are unique that approach could become awkward. My thinking is that there would be a main form based on tblEmployees, with a subform based on the junction table. Aria: Do I need a subform if the majority of the staff only hold one title? Yes, I still trying to get over having empty fields but I haven't made it this far so I don't know. The form I envision would have this info on the main form using a combobox for the titles. I am trying to think of every possible title that I can. I don't want any suprises here. Bruce M: If you would like to do something such as generate reports that list employees by title, the junction table is probably the best way to go (or the most flexible, at least). Aria: That is exactly what I want to do; grouped by title. I think I'll do the junction table. Bruce M: You may do well to start a new thread on this specific topic. I'm not sure I have the experience to advise you confidently that one choice is better than another in a particular situation. Aria: I know I'm struggling but sometimes that's o.k. (I can't believe I said *that*!) Let me qualify that remark by saying it depends on the hour. I know I need to learn how to figure out some of these issues myself. I have to try first. If I continue to have problems, I will of course follow your advice (you and Beetle). Bruce M: The thing is that this database is about keys, but once you have an Employee table you will probably use it again and again (as a linked table in other projects), so it is well to design it as carefully as can be done. Aria: Absolutely!!! You know our history here (some bad memories). Keys are problematic for us. I have always said that I don't mind putting in the hard work at the beginning so that it will seem effortless in the end. Well, here it is. I am willing to put in the effort but some days I am frustrated beyond belief. I had to add a phone table because there are a possiblity of 3 phone #s that a staff member may have aside from the room phone#, which I moved to tblLocations per our previous discussion. I think this is a 1:M relationship. Bruce M: One employee: several phones is one-to-many, but if a phone could have several people answering it, depending on the time of day, you may need a jPhonePerson unction table. StartTime and EndTime may be fields in the junction table for the room phones. Ideally this would be linked to scheduling, so that a class schedule would indicate when an employee could be reached at a particular room phone (or something like that), but that could become quite complex. Aria: Whew! Thank goodness I don't have to worry about that. A room phone could have several people answer it. This will depend on who is assigned to the room. The good thing is that they don't roam. All bets are off for the upcoming school year though. I've heard rumors. I should be getting a room assignment list from the Principal soon. We'll see.... Aria: We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. Bruce M: If so, you could just store the department in tblEmployees. However, be sure of this, as it is moredifficult to rearrange the database later. Aria: Ugh! Nooooooo...I was so sure! I checked over and over and over again and last night I finally found it...one person, two departments. Will this never end?! "Beetle" wrote: One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of information there. I had to really pay attention though and relate it to my situation. After reading it, I'm not as frustrated. Yes, there are 1:1 relationships here but it may be what I need. I do think that I have sub-types. Let me work with this some more before I let you both know which 1:1 relationships I have. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. Great...I think I have at least one situation where this is true. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions. Hey, perfect example! Hopefully this will help you. If you still have questions, as Bruce said it may be helpbul to start a new thread. He and I may be the only ones still following this thread. It does help. Thank you for that. I want to continue trying before I throw in the towel and ask for help. Comments for both of you: 1. You two are *great* teachers who always give me homework! There is such a wealth of information, instructions and suggestions that you put into your replies that I can't post back right away; I'm studying, highlighting and taking notes! 2. I've learned SO much from you (the collective you). I consider myself incredibly fortunate that you responded to my post. 3. I have varying degrees of frustration and confusion. I'm confused by some of my own table names (yeah, I know that doesn't even begin to make sense to you. I'll work on more logical naming). I'm also confusing myself by thinking to hard and long about some of these issues. I know the answer is right in front of me but I can't see it. 4. Like I said before, you both pan wide and then zoom in, incrementally, to get to the crux of a problem. I want to do that. This is starting to come into focus a little more now. I just have to keep reminding myself that problems I encounter now, will be be something I won't have to deal with later after everything is set. It's frustrating but I'm trying to keep it in perspective. 5. See #3. I'm going to have to take a step back. I have been working on this day and night (in between my day job). I have been thinking about database design in my sleep (I know, I've got it bad). 6. I have taken up so much of your time already. You have moved me forward by leaps and bounds. I understand that there are others who need help too. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). You have no idea how dejected I was when I first posted, but every question answered unraveled the knot just a little bit more. I'm going to take a couple of days and see if I can work out this employee table. When I return, I would like to get back to tblLocations, tblKeys, etc. I hope you will still be here, but if not, I completely understand. I just wanted you to know how grateful I am. I'm sure this is more than you *ever* wanted to know about locks, keys and school "issues". -- Aria W. "Beetle" wrote: The Employee section of the db seems to be expanding. I'm still struggling to work it out. As far as I can see, there seems to be some 1:1 relationships. As it stands now, there are at least 3 junction tables. One thing is clear though, no matter how I work this, there will be empty fields in some of the tables. I guess I *am* going to have to live with it. One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. ************************************************** * When it comes to people connected to the church in some way or other things get a little more complex, but not frighteningly so, as the principles involved are quite simple. All people share certain attribute types of course; we all have names, an address, a date of birth and so on. So there is an entity type People with these common attribute types, and a table can represent this attribute type. People with different roles may well have attribute types which are specific to their role, e.g. a pastor is likely to have attribute types which a member of his congregation would not have. If I can use an example from the C of E an attribute type for a C of E priest might be Date of Ordination. So the entity type Pastors is a sub-type of the entity type People. The way a sub-type is modelled in a relational data base is by means of a one-to-one relationship. In the case of People and its sub-type Pastors this would mean that there would be a table people with a numeric primary key PersonID (don't use names as a primary key, they can be duplicated), and a Pastors table also with a numeric primary key PersonID (you can call it PastorID if you wish, but I prefer to keep the column names the same). In the case of the Pastors table PersonID would also be a foreign key referencing the primary key of People. The people table would have columns for the common attributes like names and address etc., the Pastors table would have columns only for those attribute type specific to the Pastors entity type, e.g. Date of Ordination, but not the common attributes like names and address. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. When it comes to events connected with a church you need to think carefully about how these fit into the model. One thing you need to be careful of is deciding whether an event type is a sub-type of another type, or whether the type of an event is an attribute type of a single entity type. You might have a topmost Events entity type with attribute type such as Event Date, Location etc and a Baptisms sub-type with attribute types such as ChildID, FatherID, MotherID, all three referencing the People table. Another way of modelling it would be to have a single Events table with an attribute type EventType, in which a value 'Baptism' would be entered. The problem with the latter approach of course is that if you also have ChildID, FatherID, MotherID columns these won't be appropriate to other types of events in the same table. In this case therefore a type/sub-type model is probably the most appropriate. In the case of other entity types, however, the second approach might be more suitable. To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions. ************************************************** ** Hopefully this will help you. If you still have questions, as Bruce said it may be helpbul to start a new thread. He and I may be the only ones still following this thread. If you need to repost, it may be helpful if you include some more detailed information about your Employees table such as; 1) What different types of employees you need to track 2) Which attributes are common to *all* employees 3) Which attributes are specific to only certain employees. -- _________ Sean Bailey "Aria" wrote: I'd like to address this part of your post 1st, Bruce: It would probably be best to keep a single thread going. Beetle and I may end up saying the same thing, or slightly different things about the same topic, in the two branches of this thread. Since there is more information, including discussions of master keys and other matters, in the other thread, let's keep the discussion there. ...as you wish. I confess my ignorance here; all of this is new to me (Access and posting). Before I was referred here by someone in my district, I had never heard of a newsgroup/discussion group before. I had never posted anywhere at anytime. I didn't realize I was creating additional threads. I think I may have messed up this thread too. I'm just not sure how all of this is supposed to work. I read the Getting Started section and read a lot of posts before launching my own but... My apologies for any confusion I may have caused. Your tblLocks is heading in the right direction. Let me suggest LocationID rather than RoomID (in case the lock is to a building or a stadium or something), and tblLocations instead of tblRooms. You could have a field in tblLocations to identify the Location type (Room, Closet, etc.). Wing, Campus, etc. are attributes of the Location. If you have linked to tblLocations, all of the information in tblLocations is available (including Wing, etc.). No need to store such items in the Locks table. I think your suggestion is perfect and rectifies the limitations encountered with tblRooms. I have made the changes and am working on incorporating this into the db. Update- Upon further reflection, I have scrapped tblSubs. No one cared for that table except me.(smile) I thought it made sense, but I don't have any experience in this area so I defer to your judgement and suggestions. I am continuing to have problems with tblEmployees. It seemed so |
#23
|
|||
|
|||
Still Struggling...
Testing...1...2...3
Hoping you're still here... I'm going to forge ahead as if you are. I am humbled by your responses; still looking out for ways to help me. Thank you. My comments follow: Bruce: For the phone table you may want to add a time-of-day field. I gather that updating such information is a once per year chore, Aria: lol ...if only. There's never a dull moment working in a school. There are never-ending changes. "Beetle" wrote: Bothered by empty fields? Good. You should be. I know in a previous post I said something along the lines of "I might just live with a couple of empty fields in the Employees table", but I was just being lazy (shame on me!). Part of the goal of normalization is to eliminate redundancy and empty or "dead" fields. In your defense, what you didn't know and what I failed to mention is that we were talking about *16* empty fields per Sub record. Perhaps if I had mentioned that in the beginning...at any rate, you provided a solution. 1)Do not use "lookup fields" in your tables. I won't. I've been to the site and printed that info and put it in my binder along with Crystal's tutorial (although I still don't understand a lot of what she is talking about.) and every post I read that I think can help me. Of course, it goes without saying that all of my highlighted, note filled posts from the two of you are included. 4)Most developers use some sort of "naming convention" to help keep track of things as the application grows in size. "...as the application grows in size?" You could have knocked me over with a feather! "...as the application grows in size." I may be naive, but I didn't expect the application to grow; at least not until it was up and running for awhile and we needed to revise or expand it. Most of the posts I read stated they had 2 or 3 tables. Initially, I had 6 and then it started to grow! Part of the reason I thought it was growing was because of the new table that you suggested to keep me out of trouble with tblSubs. My growing application left me feeling *very* unsettled. I thought something was wrong. The other problem I had was somethng you mentioned earlier in your sentence "..to help keep track of things." I was having trouble tracking what was happening; a situation that was not helped by some of the table names I used (too many tables with the word Employee in it) and the fact that I kept adding notes and comments to my diagram so that I couldn't see anything. I had spun off School Data from the employee table like we discussed, but I had also put Emergency Info into a seperate table too. All of these stupid mistakes were of my own doing. I kept wondering if my tables were normalized. So, I was trying to compact it and was having trouble. But something good may have come out of all that angst. You (both of you) hammered home the concepts of attributes and sub-types. I think it "stuck". Beetle, I also think relationship type may have "stuck" because you used my own words and application as an example. I think it has... I hope it has... I was wondering if you could take a look at what came out of that time and tell me if I've stepped off the cliff. This is just the part for Employees. If you're done here (as we discussed earlier), don't post back. I understand... ************************************************** *********************************** tblEmployees (sfrm) tblSiteEmp (1:1) ********** ******** Inactive Yes/No SiteEmpID (PK) EmpID (PK) Autonumber, long integer EmpID (FK) number, long integer ClassID (FK to tblClassifications) DeptsSubjectsID (FK) number, long integer EmpTitleID (FK to tblEmpTitles) (FK to tblDeptsSubjects) PhoneID (FK to tblEmpTitles) Address-txt LN- txt City-txt FN-txt State-txt MI-txt Emergency Info (Hospital, Medications, Allergies etc.) DateEntered Date/Time DateModified Date/Time tblPhones(sfrm) 1:M *************** PhoneID (PK) Autonumber, long integer HomePhone-txt CellPhone-txt DistrictCell-txt DateEntered-Date/Time DateModified-Date/Time tblTitles 1:M tblSubjects 1:M ********* *********** TitlesID (PK) Autonumber, long integer SubjectsID (PK) Autonumber, long integer TitleName-txt SubjectName-txt tblClassifications 1:M tblDepts 1:M *************** ********* ClassID (PK) Autonumber, long integer DeptsID (PK) Autonumber, long integer ClassName-txt DeptsName-txt tblTitlesEmps M:M tblDeptsSubjects M:M ************* **************** TitlesEmpsID (PK) Autonumber DeptsSubjectsID (PK) Autonumber EmpID (FK to tblEmps) SubjectsID (FK to tblSubjects) TitlesID (FK to tblTitles) DeptsID (FK to tblDepts) *All FK keys are number, long integer Relationships: tblSiteEmp 1:1 tblEmps tblPhones 1:M tblEmps tblTitles 1:M tblTitlesEmps tblClassifications 1:M tblEmps tblTitlesEmps M:M tblEmps tblSubjects 1:M tblDeptsSubjects tblDepts 1:M tblDeptsSubjects tblDeptsSubjects M:M tblSiteEmp -- Aria W. "Beetle" wrote: I would echo what Bruce said and thank you for your kind reply. Here are a few additional thoughts of my own. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). Happy to help. At least you want to learn and do things the right way. The posters we don't like are the ones that have already decided how they want to do things, even though it may be wrong, and expect someone here to explain to them how to do it the *wrong* way. Bothered by empty fields? Good. You should be. I know in a previous post I said something along the lines of "I might just live with a couple of empty fields in the Employees table", but I was just being lazy (shame on me!). Part of the goal of normalization is to eliminate redundancy and empty or "dead" fields. If an attribute doesn't apply to all employees, all the time, then it is an attribute of a sub-type, and therefore probably belongs in another table. Some other things to keep in mind for the future in the development cycle of your application; 1)Do not use "lookup fields" in your tables. This basically ampunts to a combo box, or list box, in a table. Combo/List boxes in forms are appropriate, but they should never be used in tables. See this link for more info; http://www.mvps.org/access/lookupfields.htm 2)Don't use spaces or other special characters in your table or field names. The same goes for queries, forms and reports (when you get to that point). Having spaces, etc. in the names will only cause you headaches later on. So instead of "Employees Table", it would more commonly be "tblEmployees". 3)Certain words are "reserved" in Access and should not be used as names of objects in your app. They are the names of different properties, etc. of the application, and if you use them as, for example, a field name it will cause problems. Two very common examples are Name and Date. See this link for a more complete list; http://support.microsoft.com/kb/286335 4)Most developers use some sort of "naming convention" to help keep track of things as the application grows in size. Everyone has some of their own variations on naming conventions, but some things are common. For example; Table names commonly start with tbl Queries start with qry (or something similar) Forms start with frm, Sub forms start with fsub, sfrm, etc. Reports start with rpt A text box (on a form or report) would start with txt A combo box (same as above) would start with cbo etc. 5)Continuing with the naming theme. When you get to the point where you start designing forms and reports, you will notice that when you add a control (text box, combo box, etc.) to a form/report, Access will give will give it a completely useless name like "Text52" or "Combo38". Do yourself a favor and name them something meaningful like "txtEmployeePhone" or "cboSelectASubject". Good luck and post back when you have more questions. -- _________ Sean Bailey "Aria" wrote: First, let me say,"WOW"! You both have given me *so much* information. Thank you! My comments are below (I'm combining posts. I didn't know how else to do this): Bruce M: You could have a Titles table and an Employees table, with a EmployeeTitle junction table. That may make some sense if several employees have the same title, but if a lot of titles are unique that approach could become awkward. My thinking is that there would be a main form based on tblEmployees, with a subform based on the junction table. Aria: Do I need a subform if the majority of the staff only hold one title? Yes, I still trying to get over having empty fields but I haven't made it this far so I don't know. The form I envision would have this info on the main form using a combobox for the titles. I am trying to think of every possible title that I can. I don't want any suprises here. Bruce M: If you would like to do something such as generate reports that list employees by title, the junction table is probably the best way to go (or the most flexible, at least). Aria: That is exactly what I want to do; grouped by title. I think I'll do the junction table. Bruce M: You may do well to start a new thread on this specific topic. I'm not sure I have the experience to advise you confidently that one choice is better than another in a particular situation. Aria: I know I'm struggling but sometimes that's o.k. (I can't believe I said *that*!) Let me qualify that remark by saying it depends on the hour. I know I need to learn how to figure out some of these issues myself. I have to try first. If I continue to have problems, I will of course follow your advice (you and Beetle). Bruce M: The thing is that this database is about keys, but once you have an Employee table you will probably use it again and again (as a linked table in other projects), so it is well to design it as carefully as can be done. Aria: Absolutely!!! You know our history here (some bad memories). Keys are problematic for us. I have always said that I don't mind putting in the hard work at the beginning so that it will seem effortless in the end. Well, here it is. I am willing to put in the effort but some days I am frustrated beyond belief. I had to add a phone table because there are a possiblity of 3 phone #s that a staff member may have aside from the room phone#, which I moved to tblLocations per our previous discussion. I think this is a 1:M relationship. Bruce M: One employee: several phones is one-to-many, but if a phone could have several people answering it, depending on the time of day, you may need a jPhonePerson unction table. StartTime and EndTime may be fields in the junction table for the room phones. Ideally this would be linked to scheduling, so that a class schedule would indicate when an employee could be reached at a particular room phone (or something like that), but that could become quite complex. Aria: Whew! Thank goodness I don't have to worry about that. A room phone could have several people answer it. This will depend on who is assigned to the room. The good thing is that they don't roam. All bets are off for the upcoming school year though. I've heard rumors. I should be getting a room assignment list from the Principal soon. We'll see.... Aria: We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. Bruce M: If so, you could just store the department in tblEmployees. However, be sure of this, as it is moredifficult to rearrange the database later. Aria: Ugh! Nooooooo...I was so sure! I checked over and over and over again and last night I finally found it...one person, two departments. Will this never end?! "Beetle" wrote: One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of information there. I had to really pay attention though and relate it to my situation. After reading it, I'm not as frustrated. Yes, there are 1:1 relationships here but it may be what I need. I do think that I have sub-types. Let me work with this some more before I let you both know which 1:1 relationships I have. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. Great...I think I have at least one situation where this is true. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions. Hey, perfect example! Hopefully this will help you. If you still have questions, as Bruce said it may be helpbul to start a new thread. He and I may be the only ones still following this thread. It does help. Thank you for that. I want to continue trying before I throw in the towel and ask for help. Comments for both of you: 1. You two are *great* teachers who always give me homework! There is such a wealth of information, instructions and suggestions that you put into your replies that I can't post back right away; I'm studying, highlighting and taking notes! 2. I've learned SO much from you (the collective you). I consider myself incredibly fortunate that you responded to my post. 3. I have varying degrees of frustration and confusion. I'm confused by some of my own table names (yeah, I know that doesn't even begin to make sense to you. I'll work on more logical naming). I'm also confusing myself by thinking to hard and long about some of these issues. I know the answer is right in front of me but I can't see it. 4. Like I said before, you both pan wide and then zoom in, incrementally, to get to the crux of a problem. I want to do that. This is starting to come into focus a little more now. I just have to keep reminding myself that problems I encounter now, will be be something I won't have to deal with later after everything is set. It's frustrating but I'm trying to keep it in perspective. 5. See #3. I'm going to have to take a step back. I have been working on this day and night (in between my day job). I have been thinking about database design in my sleep (I know, I've got it bad). 6. I have taken up so much of your time already. You have moved me forward by leaps and bounds. I understand that there are others who need help too. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). You have no idea how dejected I was when I first posted, but every question answered unraveled the knot just a little bit more. I'm going to take a couple of days and see if I can work out this employee table. When I return, I would like to get back to tblLocations, tblKeys, etc. I hope you will still be here, but if not, I completely understand. I just wanted you to know how grateful I am. I'm sure this is more than you *ever* wanted to know about locks, keys and school "issues". -- Aria W. "Beetle" wrote: The Employee section of the db seems to be expanding. I'm still struggling to work it out. As far as I can see, there seems to be some 1:1 relationships. As it stands now, there are at least 3 junction tables. One thing is clear though, no matter how I work this, there will be empty fields in some of the tables. I guess I *am* going to have to live with it. One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. ************************************************** * When it comes to people connected to the church in some way or other things get a little more complex, but not frighteningly so, as the principles involved are quite simple. All people share certain attribute types of course; we all have names, an address, a date of birth and so on. So there is an entity type People with these common attribute types, and a table can represent this attribute type. People with different roles may well have attribute types which are specific to their role, e.g. a pastor is likely to have attribute types which a member of his congregation would not have. If I can use an example from the C of E an attribute type for a C of E priest might be Date of Ordination. So the entity type Pastors is a sub-type of the entity type People. The way a sub-type is modelled in a relational data base is by means of a one-to-one relationship. In the case of People and its sub-type Pastors this would mean that there would be a table people with a numeric primary key PersonID (don't use names as a primary key, they can be duplicated), and a Pastors table also with a numeric primary key PersonID (you can call it PastorID if you wish, but I prefer to keep the column names the same). In the case of the Pastors table PersonID would also be a foreign key referencing the primary key of People. The people table would have columns for the common attributes like names and address etc., the Pastors table would have columns only for those attribute type specific to the Pastors entity type, e.g. Date of Ordination, but not the common attributes like names and address. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. |
#24
|
|||
|
|||
Still Struggling...
Well, I learned something new. Let's try this again so that's it's readable.
************************************************** *********************************** tblEmployees ********** Inactive Yes/No EmpID (PK) Autonumber, long integer ClassID (FK to tblClassifications) EmpTitleID (FK to tblEmpTitles) PhoneID (FK to tblEmpTitles) LN- txt FN-txt MI-txt (sfrm) tblSiteEmp (1:1) SiteEmpID (PK) EmpID (FK) number, long integer DeptsSubjectsID (FK) number, long integer Address-txt City-txt State-txt Emergency Info(Hospital, Medications, Allergies etc.) DateEntered Date/Time DateModified Date/Time tblPhones(sfrm) 1:M *************** PhoneID (PK) Autonumber, long integer HomePhone-txt CellPhone-txt DistrictCell-txt DateEntered-Date/Time DateModified-Date/Time tblTitles 1:M ********* TitlesID (PK) Autonumber, long integer TitleName-txt tblSubjects 1:M *********** SubjectsID (PK) Autonumber, long integer SubjectName-txt tblDepts 1:M ********* DeptsID (PK) Autonumber, long integer DeptsName-txt tblDeptsSubjects M:M ************ DeptsSubjectsID (PK) Autonumber SubjectsID (FK to tblSubjects) DeptsID (FK to tblDepts) tblClassifications 1:M *************** ClassID (PK) Autonumber, long integer ClassName-txt tblTitlesEmps M:M ************* TitlesEmpsID (PK) Autonumber EmpID (FK to tblEmps) TitlesID (FK to tblTitles) *All FK keys are number, long integer Relationships: tblSiteEmp 1:1 tblEmps tblPhones 1:M tblEmps tblTitles 1:M tblTitlesEmps tblClassifications 1:M tblEmps tblTitlesEmps M:M tblEmps tblSubjects 1:M tblDeptsSubjects tblDepts 1:M tblDeptsSubjects tblDeptsSubjects M:M tblSiteEmp -- Aria W. "Beetle" wrote: I would echo what Bruce said and thank you for your kind reply. Here are a few additional thoughts of my own. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). Happy to help. At least you want to learn and do things the right way. The posters we don't like are the ones that have already decided how they want to do things, even though it may be wrong, and expect someone here to explain to them how to do it the *wrong* way. Bothered by empty fields? Good. You should be. I know in a previous post I said something along the lines of "I might just live with a couple of empty fields in the Employees table", but I was just being lazy (shame on me!). Part of the goal of normalization is to eliminate redundancy and empty or "dead" fields. If an attribute doesn't apply to all employees, all the time, then it is an attribute of a sub-type, and therefore probably belongs in another table. Some other things to keep in mind for the future in the development cycle of your application; 1)Do not use "lookup fields" in your tables. This basically ampunts to a combo box, or list box, in a table. Combo/List boxes in forms are appropriate, but they should never be used in tables. See this link for more info; http://www.mvps.org/access/lookupfields.htm 2)Don't use spaces or other special characters in your table or field names. The same goes for queries, forms and reports (when you get to that point). Having spaces, etc. in the names will only cause you headaches later on. So instead of "Employees Table", it would more commonly be "tblEmployees". 3)Certain words are "reserved" in Access and should not be used as names of objects in your app. They are the names of different properties, etc. of the application, and if you use them as, for example, a field name it will cause problems. Two very common examples are Name and Date. See this link for a more complete list; http://support.microsoft.com/kb/286335 4)Most developers use some sort of "naming convention" to help keep track of things as the application grows in size. Everyone has some of their own variations on naming conventions, but some things are common. For example; Table names commonly start with tbl Queries start with qry (or something similar) Forms start with frm, Sub forms start with fsub, sfrm, etc. Reports start with rpt A text box (on a form or report) would start with txt A combo box (same as above) would start with cbo etc. 5)Continuing with the naming theme. When you get to the point where you start designing forms and reports, you will notice that when you add a control (text box, combo box, etc.) to a form/report, Access will give will give it a completely useless name like "Text52" or "Combo38". Do yourself a favor and name them something meaningful like "txtEmployeePhone" or "cboSelectASubject". Good luck and post back when you have more questions. -- _________ Sean Bailey "Aria" wrote: First, let me say,"WOW"! You both have given me *so much* information. Thank you! My comments are below (I'm combining posts. I didn't know how else to do this): Bruce M: You could have a Titles table and an Employees table, with a EmployeeTitle junction table. That may make some sense if several employees have the same title, but if a lot of titles are unique that approach could become awkward. My thinking is that there would be a main form based on tblEmployees, with a subform based on the junction table. Aria: Do I need a subform if the majority of the staff only hold one title? Yes, I still trying to get over having empty fields but I haven't made it this far so I don't know. The form I envision would have this info on the main form using a combobox for the titles. I am trying to think of every possible title that I can. I don't want any suprises here. Bruce M: If you would like to do something such as generate reports that list employees by title, the junction table is probably the best way to go (or the most flexible, at least). Aria: That is exactly what I want to do; grouped by title. I think I'll do the junction table. Bruce M: You may do well to start a new thread on this specific topic. I'm not sure I have the experience to advise you confidently that one choice is better than another in a particular situation. Aria: I know I'm struggling but sometimes that's o.k. (I can't believe I said *that*!) Let me qualify that remark by saying it depends on the hour. I know I need to learn how to figure out some of these issues myself. I have to try first. If I continue to have problems, I will of course follow your advice (you and Beetle). Bruce M: The thing is that this database is about keys, but once you have an Employee table you will probably use it again and again (as a linked table in other projects), so it is well to design it as carefully as can be done. Aria: Absolutely!!! You know our history here (some bad memories). Keys are problematic for us. I have always said that I don't mind putting in the hard work at the beginning so that it will seem effortless in the end. Well, here it is. I am willing to put in the effort but some days I am frustrated beyond belief. I had to add a phone table because there are a possiblity of 3 phone #s that a staff member may have aside from the room phone#, which I moved to tblLocations per our previous discussion. I think this is a 1:M relationship. Bruce M: One employee: several phones is one-to-many, but if a phone could have several people answering it, depending on the time of day, you may need a jPhonePerson unction table. StartTime and EndTime may be fields in the junction table for the room phones. Ideally this would be linked to scheduling, so that a class schedule would indicate when an employee could be reached at a particular room phone (or something like that), but that could become quite complex. Aria: Whew! Thank goodness I don't have to worry about that. A room phone could have several people answer it. This will depend on who is assigned to the room. The good thing is that they don't roam. All bets are off for the upcoming school year though. I've heard rumors. I should be getting a room assignment list from the Principal soon. We'll see.... Aria: We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. Bruce M: If so, you could just store the department in tblEmployees. However, be sure of this, as it is moredifficult to rearrange the database later. Aria: Ugh! Nooooooo...I was so sure! I checked over and over and over again and last night I finally found it...one person, two departments. Will this never end?! "Beetle" wrote: One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of information there. I had to really pay attention though and relate it to my situation. After reading it, I'm not as frustrated. Yes, there are 1:1 relationships here but it may be what I need. I do think that I have sub-types. Let me work with this some more before I let you both know which 1:1 relationships I have. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. Great...I think I have at least one situation where this is true. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions. Hey, perfect example! Hopefully this will help you. If you still have questions, as Bruce said it may be helpbul to start a new thread. He and I may be the only ones still following this thread. It does help. Thank you for that. I want to continue trying before I throw in the towel and ask for help. Comments for both of you: 1. You two are *great* teachers who always give me homework! There is such a wealth of information, instructions and suggestions that you put into your replies that I can't post back right away; I'm studying, highlighting and taking notes! 2. I've learned SO much from you (the collective you). I consider myself incredibly fortunate that you responded to my post. 3. I have varying degrees of frustration and confusion. I'm confused by some of my own table names (yeah, I know that doesn't even begin to make sense to you. I'll work on more logical naming). I'm also confusing myself by thinking to hard and long about some of these issues. I know the answer is right in front of me but I can't see it. 4. Like I said before, you both pan wide and then zoom in, incrementally, to get to the crux of a problem. I want to do that. This is starting to come into focus a little more now. I just have to keep reminding myself that problems I encounter now, will be be something I won't have to deal with later after everything is set. It's frustrating but I'm trying to keep it in perspective. 5. See #3. I'm going to have to take a step back. I have been working on this day and night (in between my day job). I have been thinking about database design in my sleep (I know, I've got it bad). 6. I have taken up so much of your time already. You have moved me forward by leaps and bounds. I understand that there are others who need help too. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). You have no idea how dejected I was when I first posted, but every question answered unraveled the knot just a little bit more. I'm going to take a couple of days and see if I can work out this employee table. When I return, I would like to get back to tblLocations, tblKeys, etc. I hope you will still be here, but if not, I completely understand. I just wanted you to know how grateful I am. I'm sure this is more than you *ever* wanted to know about locks, keys and school "issues". -- Aria W. "Beetle" wrote: The Employee section of the db seems to be expanding. I'm still struggling to work it out. As far as I can see, there seems to be some 1:1 relationships. As it stands now, there are at least 3 junction tables. One thing is clear though, no matter how I work this, there will be empty fields in some of the tables. I guess I *am* going to have to live with it. One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. ************************************************** * When it comes to people connected to the church in some way or other things get a little more complex, but not frighteningly so, as the principles involved are quite simple. All people share certain attribute types of course; we all have names, an address, a date of birth and so on. So there is an entity type People with these common attribute types, and a table can represent this attribute type. People with different roles may well have attribute types which are specific to their role, e.g. a pastor is likely to have attribute types which a member of his congregation would not have. If I can use an example from the C of E an attribute type for a C of E priest might be Date of Ordination. So the entity type Pastors is a sub-type of the entity type People. The way a sub-type is modelled in a relational data base is by means of a one-to-one relationship. In the case of People and its sub-type Pastors this would mean that there would be a table people with a numeric primary key PersonID (don't use names as a primary key, they can be duplicated), and a Pastors table also with a numeric primary key PersonID (you can call it PastorID if you wish, but I prefer to keep the column names the same). In the case of the Pastors table PersonID would also be a foreign key referencing the primary key of People. The people table would have columns for the common attributes like names and address etc., the Pastors table would have columns only for those attribute type specific to the Pastors entity type, e.g. Date of Ordination, but not the common attributes like names and address. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. |
#25
|
|||
|
|||
Still Struggling...
Responses inline.
"Aria" wrote in message ... Well, I learned something new. Let's try this again so that's it's readable. ************************************************** *********************************** tblEmployees ********** Inactive Yes/No EmpID (PK) Autonumber, long integer ClassID (FK to tblClassifications) EmpTitleID (FK to tblEmpTitles) PhoneID (FK to tblEmpTitles) LN- txt FN-txt MI-txt If one employee can have several classifications, EmpID is a FK in tblClassifications. If a classification can be used for several employees but each employee can have only one classification, your approach may be the correct one. If an employee can have several classifications and vice versa, a junction table is indicated. The same would apply to tblEmpTitles. I'm not following how PhoneID is a FK to tblEmpTitles. If one employee may have several phones, that is one-to-many. If a phone may be used by several people depending on when they are in a particular room, the phone is an attribute of the room, in a sense. On the other hand, presumably some phones are for offices used by just one person. In that case the phone number would probably go with the person if they move to another office, so the phone is an attribute of the person rather than the room. Cell phones cannot be considered attributes of a room. Ordinarily I would have a one employee several phones situation. Each phone would be associated with a single employee. The Phone table could have a PhoneLocation field, and maybe a TimeOfDay field for phones in shared rooms. Frankly, I would be inclined to go with the same approach for now. If you enter the same number for several different employees because they are in the same room at different times, so be it. Anything else that incorporates private phones and shared phones may be needlessly complex for now. If you are determined to make tblEmployee as flexible as you can right away we may be able to come up with a strategy, but this may be bogging you down in the short term if you are trying to keep track of keys. (sfrm) tblSiteEmp (1:1) SiteEmpID (PK) EmpID (FK) number, long integer DeptsSubjectsID (FK) number, long integer Address-txt City-txt State-txt Emergency Info(Hospital, Medications, Allergies etc.) DateEntered Date/Time DateModified Date/Time I think Address information should be in the main employee table, unless each employee could have several addresses (a summner address and a school year address, for instance). In that case, a related Address table is needed. Emergency information maybe should be in its own table, 1:M from tblEmployees. I'm afraid that's all I have time to write now. I may not be available much next week, but I'll check in with this thread if I can. tblPhones(sfrm) 1:M *************** PhoneID (PK) Autonumber, long integer HomePhone-txt CellPhone-txt DistrictCell-txt DateEntered-Date/Time DateModified-Date/Time tblTitles 1:M ********* TitlesID (PK) Autonumber, long integer TitleName-txt tblSubjects 1:M *********** SubjectsID (PK) Autonumber, long integer SubjectName-txt tblDepts 1:M ********* DeptsID (PK) Autonumber, long integer DeptsName-txt tblDeptsSubjects M:M ************ DeptsSubjectsID (PK) Autonumber SubjectsID (FK to tblSubjects) DeptsID (FK to tblDepts) tblClassifications 1:M *************** ClassID (PK) Autonumber, long integer ClassName-txt tblTitlesEmps M:M ************* TitlesEmpsID (PK) Autonumber EmpID (FK to tblEmps) TitlesID (FK to tblTitles) *All FK keys are number, long integer Relationships: tblSiteEmp 1:1 tblEmps tblPhones 1:M tblEmps tblTitles 1:M tblTitlesEmps tblClassifications 1:M tblEmps tblTitlesEmps M:M tblEmps tblSubjects 1:M tblDeptsSubjects tblDepts 1:M tblDeptsSubjects tblDeptsSubjects M:M tblSiteEmp -- Aria W. "Beetle" wrote: I would echo what Bruce said and thank you for your kind reply. Here are a few additional thoughts of my own. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). Happy to help. At least you want to learn and do things the right way. The posters we don't like are the ones that have already decided how they want to do things, even though it may be wrong, and expect someone here to explain to them how to do it the *wrong* way. Bothered by empty fields? Good. You should be. I know in a previous post I said something along the lines of "I might just live with a couple of empty fields in the Employees table", but I was just being lazy (shame on me!). Part of the goal of normalization is to eliminate redundancy and empty or "dead" fields. If an attribute doesn't apply to all employees, all the time, then it is an attribute of a sub-type, and therefore probably belongs in another table. Some other things to keep in mind for the future in the development cycle of your application; 1)Do not use "lookup fields" in your tables. This basically ampunts to a combo box, or list box, in a table. Combo/List boxes in forms are appropriate, but they should never be used in tables. See this link for more info; http://www.mvps.org/access/lookupfields.htm 2)Don't use spaces or other special characters in your table or field names. The same goes for queries, forms and reports (when you get to that point). Having spaces, etc. in the names will only cause you headaches later on. So instead of "Employees Table", it would more commonly be "tblEmployees". 3)Certain words are "reserved" in Access and should not be used as names of objects in your app. They are the names of different properties, etc. of the application, and if you use them as, for example, a field name it will cause problems. Two very common examples are Name and Date. See this link for a more complete list; http://support.microsoft.com/kb/286335 4)Most developers use some sort of "naming convention" to help keep track of things as the application grows in size. Everyone has some of their own variations on naming conventions, but some things are common. For example; Table names commonly start with tbl Queries start with qry (or something similar) Forms start with frm, Sub forms start with fsub, sfrm, etc. Reports start with rpt A text box (on a form or report) would start with txt A combo box (same as above) would start with cbo etc. 5)Continuing with the naming theme. When you get to the point where you start designing forms and reports, you will notice that when you add a control (text box, combo box, etc.) to a form/report, Access will give will give it a completely useless name like "Text52" or "Combo38". Do yourself a favor and name them something meaningful like "txtEmployeePhone" or "cboSelectASubject". Good luck and post back when you have more questions. -- _________ Sean Bailey "Aria" wrote: First, let me say,"WOW"! You both have given me *so much* information. Thank you! My comments are below (I'm combining posts. I didn't know how else to do this): Bruce M: You could have a Titles table and an Employees table, with a EmployeeTitle junction table. That may make some sense if several employees have the same title, but if a lot of titles are unique that approach could become awkward. My thinking is that there would be a main form based on tblEmployees, with a subform based on the junction table. Aria: Do I need a subform if the majority of the staff only hold one title? Yes, I still trying to get over having empty fields but I haven't made it this far so I don't know. The form I envision would have this info on the main form using a combobox for the titles. I am trying to think of every possible title that I can. I don't want any suprises here. Bruce M: If you would like to do something such as generate reports that list employees by title, the junction table is probably the best way to go (or the most flexible, at least). Aria: That is exactly what I want to do; grouped by title. I think I'll do the junction table. Bruce M: You may do well to start a new thread on this specific topic. I'm not sure I have the experience to advise you confidently that one choice is better than another in a particular situation. Aria: I know I'm struggling but sometimes that's o.k. (I can't believe I said *that*!) Let me qualify that remark by saying it depends on the hour. I know I need to learn how to figure out some of these issues myself. I have to try first. If I continue to have problems, I will of course follow your advice (you and Beetle). Bruce M: The thing is that this database is about keys, but once you have an Employee table you will probably use it again and again (as a linked table in other projects), so it is well to design it as carefully as can be done. Aria: Absolutely!!! You know our history here (some bad memories). Keys are problematic for us. I have always said that I don't mind putting in the hard work at the beginning so that it will seem effortless in the end. Well, here it is. I am willing to put in the effort but some days I am frustrated beyond belief. I had to add a phone table because there are a possiblity of 3 phone #s that a staff member may have aside from the room phone#, which I moved to tblLocations per our previous discussion. I think this is a 1:M relationship. Bruce M: One employee: several phones is one-to-many, but if a phone could have several people answering it, depending on the time of day, you may need a jPhonePerson unction table. StartTime and EndTime may be fields in the junction table for the room phones. Ideally this would be linked to scheduling, so that a class schedule would indicate when an employee could be reached at a particular room phone (or something like that), but that could become quite complex. Aria: Whew! Thank goodness I don't have to worry about that. A room phone could have several people answer it. This will depend on who is assigned to the room. The good thing is that they don't roam. All bets are off for the upcoming school year though. I've heard rumors. I should be getting a room assignment list from the Principal soon. We'll see.... Aria: We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. Bruce M: If so, you could just store the department in tblEmployees. However, be sure of this, as it is moredifficult to rearrange the database later. Aria: Ugh! Nooooooo...I was so sure! I checked over and over and over again and last night I finally found it...one person, two departments. Will this never end?! "Beetle" wrote: One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of information there. I had to really pay attention though and relate it to my situation. After reading it, I'm not as frustrated. Yes, there are 1:1 relationships here but it may be what I need. I do think that I have sub-types. Let me work with this some more before I let you both know which 1:1 relationships I have. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. Great...I think I have at least one situation where this is true. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions. Hey, perfect example! Hopefully this will help you. If you still have questions, as Bruce said it may be helpbul to start a new thread. He and I may be the only ones still following this thread. It does help. Thank you for that. I want to continue trying before I throw in the towel and ask for help. Comments for both of you: 1. You two are *great* teachers who always give me homework! There is such a wealth of information, instructions and suggestions that you put into your replies that I can't post back right away; I'm studying, highlighting and taking notes! 2. I've learned SO much from you (the collective you). I consider myself incredibly fortunate that you responded to my post. 3. I have varying degrees of frustration and confusion. I'm confused by some of my own table names (yeah, I know that doesn't even begin to make sense to you. I'll work on more logical naming). I'm also confusing myself by thinking to hard and long about some of these issues. I know the answer is right in front of me but I can't see it. 4. Like I said before, you both pan wide and then zoom in, incrementally, to get to the crux of a problem. I want to do that. This is starting to come into focus a little more now. I just have to keep reminding myself that problems I encounter now, will be be something I won't have to deal with later after everything is set. It's frustrating but I'm trying to keep it in perspective. 5. See #3. I'm going to have to take a step back. I have been working on this day and night (in between my day job). I have been thinking about database design in my sleep (I know, I've got it bad). 6. I have taken up so much of your time already. You have moved me forward by leaps and bounds. I understand that there are others who need help too. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). You have no idea how dejected I was when I first posted, but every question answered unraveled the knot just a little bit more. I'm going to take a couple of days and see if I can work out this employee table. When I return, I would like to get back to tblLocations, tblKeys, etc. I hope you will still be here, but if not, I completely understand. I just wanted you to know how grateful I am. I'm sure this is more than you *ever* wanted to know about locks, keys and school "issues". -- Aria W. "Beetle" wrote: The Employee section of the db seems to be expanding. I'm still struggling to work it out. As far as I can see, there seems to be some 1:1 relationships. As it stands now, there are at least 3 junction tables. One thing is clear though, no matter how I work this, there will be empty fields in some of the tables. I guess I *am* going to have to live with it. One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. ************************************************** * When it comes to people connected to the church in some way or other things get a little more complex, but not frighteningly so, as the principles involved are quite simple. All people share certain attribute types of course; we all have names, an address, a date of birth and so on. So there is an entity type People with these common attribute types, and a table can represent this attribute type. People with different roles may well have attribute types which are specific to their role, e.g. a pastor is likely to have attribute types which a member of his congregation would not have. If I can use an example from the C of E an attribute type for a C of E priest might be Date of Ordination. So the entity type Pastors is a sub-type of the entity type People. The way a sub-type is modelled in a relational data base is by means of a one-to-one relationship. In the case of People and its sub-type Pastors this would mean that there would be a table people with a numeric primary key PersonID (don't use names as a primary key, they can be duplicated), and a Pastors table also with a numeric primary key PersonID (you can call it PastorID if you wish, but I prefer to keep the column names the same). In the case of the Pastors table PersonID would also be a foreign key referencing the primary key of People. The people table would have columns for the common attributes like names and address etc., the Pastors table would have columns only for those attribute type specific to the Pastors entity type, e.g. Date of Ordination, but not the common attributes like names and address. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. |
#26
|
|||
|
|||
Still Struggling...
I'm sorry. I messed it up. Sorry Bruce. Here is the revised version. Have a
great weekend! tblEmployees ********** Inactive Yes/No EmpID (PK) Autonumber, long integer ClassID (FK to tblClassifications) EmpTitleID (FK to tblEmpTitles) PhoneID (FK to tblPhones) LN- txt FN-txt MI-txt (sfrm) tblSiteEmp (1:1) ************ SiteEmpID (PK) EmpID (FK) number, long integer DeptsSubjectsID (FK) number, long integer Address-txt City-txt State-txt Emergency Info(Hospital, Medications, Allergies etc.) DateEntered Date/Time DateModified Date/Time tblPhones (sfrm) 1:M *************** PhoneID (PK) Autonumber, long integer HomePhone-txt CellPhone-txt DistrictCell-txt DateEntered-Date/Time DateModified-Date/Time tblTitles 1:M ********* TitlesID (PK) Autonumber, long integer TitleName-txt tblSubjects 1:M *********** SubjectsID (PK) Autonumber, long integer SubjectName-txt tblDepts 1:M ********* DeptsID (PK) Autonumber, long integer DeptsName-txt tblDeptsSubjects M:M ************ DeptsSubjectsID (PK) Autonumber SubjectsID (FK to tblSubjects) DeptsID (FK to tblDepts) tblClassifications 1:M *************** ClassID (PK) Autonumber, long integer ClassName-txt tblTitlesEmps M:M ************* TitlesEmpsID (PK) Autonumber EmpID (FK to tblEmps) TitlesID (FK to tblTitles) *All FK keys are number, long integer Relationships: tblSiteEmp 1:1 tblEmps tblPhones 1:M tblEmps tblTitles 1:M tblTitlesEmps tblClassifications 1:M tblEmps tblTitlesEmps M:M tblEmps tblSubjects 1:M tblDeptsSubjects tblDepts 1:M tblDeptsSubjects tblDeptsSubjects M:M tblSiteEmp -- Aria W. "Beetle" wrote: I would echo what Bruce said and thank you for your kind reply. Here are a few additional thoughts of my own. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). Happy to help. At least you want to learn and do things the right way. The posters we don't like are the ones that have already decided how they want to do things, even though it may be wrong, and expect someone here to explain to them how to do it the *wrong* way. Bothered by empty fields? Good. You should be. I know in a previous post I said something along the lines of "I might just live with a couple of empty fields in the Employees table", but I was just being lazy (shame on me!). Part of the goal of normalization is to eliminate redundancy and empty or "dead" fields. If an attribute doesn't apply to all employees, all the time, then it is an attribute of a sub-type, and therefore probably belongs in another table. Some other things to keep in mind for the future in the development cycle of your application; 1)Do not use "lookup fields" in your tables. This basically ampunts to a combo box, or list box, in a table. Combo/List boxes in forms are appropriate, but they should never be used in tables. See this link for more info; http://www.mvps.org/access/lookupfields.htm 2)Don't use spaces or other special characters in your table or field names. The same goes for queries, forms and reports (when you get to that point). Having spaces, etc. in the names will only cause you headaches later on. So instead of "Employees Table", it would more commonly be "tblEmployees". 3)Certain words are "reserved" in Access and should not be used as names of objects in your app. They are the names of different properties, etc. of the application, and if you use them as, for example, a field name it will cause problems. Two very common examples are Name and Date. See this link for a more complete list; http://support.microsoft.com/kb/286335 4)Most developers use some sort of "naming convention" to help keep track of things as the application grows in size. Everyone has some of their own variations on naming conventions, but some things are common. For example; Table names commonly start with tbl Queries start with qry (or something similar) Forms start with frm, Sub forms start with fsub, sfrm, etc. Reports start with rpt A text box (on a form or report) would start with txt A combo box (same as above) would start with cbo etc. 5)Continuing with the naming theme. When you get to the point where you start designing forms and reports, you will notice that when you add a control (text box, combo box, etc.) to a form/report, Access will give will give it a completely useless name like "Text52" or "Combo38". Do yourself a favor and name them something meaningful like "txtEmployeePhone" or "cboSelectASubject". Good luck and post back when you have more questions. -- _________ Sean Bailey "Aria" wrote: First, let me say,"WOW"! You both have given me *so much* information. Thank you! My comments are below (I'm combining posts. I didn't know how else to do this): Bruce M: You could have a Titles table and an Employees table, with a EmployeeTitle junction table. That may make some sense if several employees have the same title, but if a lot of titles are unique that approach could become awkward. My thinking is that there would be a main form based on tblEmployees, with a subform based on the junction table. Aria: Do I need a subform if the majority of the staff only hold one title? Yes, I still trying to get over having empty fields but I haven't made it this far so I don't know. The form I envision would have this info on the main form using a combobox for the titles. I am trying to think of every possible title that I can. I don't want any suprises here. Bruce M: If you would like to do something such as generate reports that list employees by title, the junction table is probably the best way to go (or the most flexible, at least). Aria: That is exactly what I want to do; grouped by title. I think I'll do the junction table. Bruce M: You may do well to start a new thread on this specific topic. I'm not sure I have the experience to advise you confidently that one choice is better than another in a particular situation. Aria: I know I'm struggling but sometimes that's o.k. (I can't believe I said *that*!) Let me qualify that remark by saying it depends on the hour. I know I need to learn how to figure out some of these issues myself. I have to try first. If I continue to have problems, I will of course follow your advice (you and Beetle). Bruce M: The thing is that this database is about keys, but once you have an Employee table you will probably use it again and again (as a linked table in other projects), so it is well to design it as carefully as can be done. Aria: Absolutely!!! You know our history here (some bad memories). Keys are problematic for us. I have always said that I don't mind putting in the hard work at the beginning so that it will seem effortless in the end. Well, here it is. I am willing to put in the effort but some days I am frustrated beyond belief. I had to add a phone table because there are a possiblity of 3 phone #s that a staff member may have aside from the room phone#, which I moved to tblLocations per our previous discussion. I think this is a 1:M relationship. Bruce M: One employee: several phones is one-to-many, but if a phone could have several people answering it, depending on the time of day, you may need a jPhonePerson unction table. StartTime and EndTime may be fields in the junction table for the room phones. Ideally this would be linked to scheduling, so that a class schedule would indicate when an employee could be reached at a particular room phone (or something like that), but that could become quite complex. Aria: Whew! Thank goodness I don't have to worry about that. A room phone could have several people answer it. This will depend on who is assigned to the room. The good thing is that they don't roam. All bets are off for the upcoming school year though. I've heard rumors. I should be getting a room assignment list from the Principal soon. We'll see.... Aria: We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. Bruce M: If so, you could just store the department in tblEmployees. However, be sure of this, as it is moredifficult to rearrange the database later. Aria: Ugh! Nooooooo...I was so sure! I checked over and over and over again and last night I finally found it...one person, two departments. Will this never end?! "Beetle" wrote: One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of information there. I had to really pay attention though and relate it to my situation. After reading it, I'm not as frustrated. Yes, there are 1:1 relationships here but it may be what I need. I do think that I have sub-types. Let me work with this some more before I let you both know which 1:1 relationships I have. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. Great...I think I have at least one situation where this is true. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions. Hey, perfect example! Hopefully this will help you. If you still have questions, as Bruce said it may be helpbul to start a new thread. He and I may be the only ones still following this thread. It does help. Thank you for that. I want to continue trying before I throw in the towel and ask for help. Comments for both of you: 1. You two are *great* teachers who always give me homework! There is such a wealth of information, instructions and suggestions that you put into your replies that I can't post back right away; I'm studying, highlighting and taking notes! 2. I've learned SO much from you (the collective you). I consider myself incredibly fortunate that you responded to my post. 3. I have varying degrees of frustration and confusion. I'm confused by some of my own table names (yeah, I know that doesn't even begin to make sense to you. I'll work on more logical naming). I'm also confusing myself by thinking to hard and long about some of these issues. I know the answer is right in front of me but I can't see it. 4. Like I said before, you both pan wide and then zoom in, incrementally, to get to the crux of a problem. I want to do that. This is starting to come into focus a little more now. I just have to keep reminding myself that problems I encounter now, will be be something I won't have to deal with later after everything is set. It's frustrating but I'm trying to keep it in perspective. 5. See #3. I'm going to have to take a step back. I have been working on this day and night (in between my day job). I have been thinking about database design in my sleep (I know, I've got it bad). 6. I have taken up so much of your time already. You have moved me forward by leaps and bounds. I understand that there are others who need help too. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). You have no idea how dejected I was when I first posted, but every question answered unraveled the knot just a little bit more. I'm going to take a couple of days and see if I can work out this employee table. When I return, I would like to get back to tblLocations, tblKeys, etc. I hope you will still be here, but if not, I completely understand. I just wanted you to know how grateful I am. I'm sure this is more than you *ever* wanted to know about locks, keys and school "issues". -- Aria W. "Beetle" wrote: The Employee section of the db seems to be expanding. I'm still struggling to work it out. As far as I can see, there seems to be some 1:1 relationships. As it stands now, there are at least 3 junction tables. One thing is clear though, no matter how I work this, there will be empty fields in some of the tables. I guess I *am* going to have to live with it. One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. ************************************************** * When it comes to people connected to the church in some way or other things get a little more complex, but not frighteningly so, as the principles involved are quite simple. All people share certain attribute types of course; we all have names, an address, a date of birth and so on. So there is an entity type People with these common attribute types, and a table can represent this attribute type. People with different roles may well have attribute types which are specific to their role, e.g. a pastor is likely to have attribute types which a member of his congregation would not have. If I can use an example from the C of E an attribute type for a C of E priest might be Date of Ordination. So the entity type Pastors is a sub-type of the entity type People. The way a sub-type is modelled in a relational data base is by means of a one-to-one relationship. In the case of People and its sub-type Pastors this would mean that there would be a table people with a numeric primary key PersonID (don't use names as a primary key, they can be duplicated), and a Pastors table also with a numeric primary key PersonID (you can call it PastorID if you wish, but I prefer to keep the column names the same). In the case of the Pastors table PersonID would also be a foreign key referencing the primary key of People. The people table would have columns for the common attributes like names and address etc., the Pastors table would have columns only for those attribute type specific to the Pastors entity type, e.g. Date of Ordination, but not the common attributes like names and address. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. As far as the primary keys are concerned, if you use an autonumber column you can only do so for the topmost type, e.g. you could have an autonumber PersonID column in the People table, but the primary key of Pastors or other sub-type of people must be a straightforward long integer number data type, not an autonumber. |
#27
|
|||
|
|||
Still Struggling...
I have printed your post and will look it over, but I do have a few questions.
1) Can an employee have more than one Classification, or can a Classification have more than one employee, or both? 2) Same as above but for Titles. You appear to have it set up as m:m but I just want to clarify. 3) You have set up a m:m relationship between Departments and Subjects which I don't quite understand. For example, the Mathematics dept. would obviously have more than one Subject, but Trigonometry would only be part of one dept. (Mathematics). I must be missing something about what you are tracking here. 4) You appear to have no relationship between an employee and the dept. they work in or the subject they teach (at least not that I can see). There are some other issues also, but I'll cover those after I have a better understanding of the above questions. -- _________ Sean Bailey "Aria" wrote: I'm sorry. I messed it up. Sorry Bruce. Here is the revised version. Have a great weekend! tblEmployees ********** Inactive Yes/No EmpID (PK) Autonumber, long integer ClassID (FK to tblClassifications) EmpTitleID (FK to tblEmpTitles) PhoneID (FK to tblPhones) LN- txt FN-txt MI-txt (sfrm) tblSiteEmp (1:1) ************ SiteEmpID (PK) EmpID (FK) number, long integer DeptsSubjectsID (FK) number, long integer Address-txt City-txt State-txt Emergency Info(Hospital, Medications, Allergies etc.) DateEntered Date/Time DateModified Date/Time tblPhones (sfrm) 1:M *************** PhoneID (PK) Autonumber, long integer HomePhone-txt CellPhone-txt DistrictCell-txt DateEntered-Date/Time DateModified-Date/Time tblTitles 1:M ********* TitlesID (PK) Autonumber, long integer TitleName-txt tblSubjects 1:M *********** SubjectsID (PK) Autonumber, long integer SubjectName-txt tblDepts 1:M ********* DeptsID (PK) Autonumber, long integer DeptsName-txt tblDeptsSubjects M:M ************ DeptsSubjectsID (PK) Autonumber SubjectsID (FK to tblSubjects) DeptsID (FK to tblDepts) tblClassifications 1:M *************** ClassID (PK) Autonumber, long integer ClassName-txt tblTitlesEmps M:M ************* TitlesEmpsID (PK) Autonumber EmpID (FK to tblEmps) TitlesID (FK to tblTitles) *All FK keys are number, long integer Relationships: tblSiteEmp 1:1 tblEmps tblPhones 1:M tblEmps tblTitles 1:M tblTitlesEmps tblClassifications 1:M tblEmps tblTitlesEmps M:M tblEmps tblSubjects 1:M tblDeptsSubjects tblDepts 1:M tblDeptsSubjects tblDeptsSubjects M:M tblSiteEmp -- Aria W. "Beetle" wrote: I would echo what Bruce said and thank you for your kind reply. Here are a few additional thoughts of my own. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). Happy to help. At least you want to learn and do things the right way. The posters we don't like are the ones that have already decided how they want to do things, even though it may be wrong, and expect someone here to explain to them how to do it the *wrong* way. Bothered by empty fields? Good. You should be. I know in a previous post I said something along the lines of "I might just live with a couple of empty fields in the Employees table", but I was just being lazy (shame on me!). Part of the goal of normalization is to eliminate redundancy and empty or "dead" fields. If an attribute doesn't apply to all employees, all the time, then it is an attribute of a sub-type, and therefore probably belongs in another table. Some other things to keep in mind for the future in the development cycle of your application; 1)Do not use "lookup fields" in your tables. This basically ampunts to a combo box, or list box, in a table. Combo/List boxes in forms are appropriate, but they should never be used in tables. See this link for more info; http://www.mvps.org/access/lookupfields.htm 2)Don't use spaces or other special characters in your table or field names. The same goes for queries, forms and reports (when you get to that point). Having spaces, etc. in the names will only cause you headaches later on. So instead of "Employees Table", it would more commonly be "tblEmployees". 3)Certain words are "reserved" in Access and should not be used as names of objects in your app. They are the names of different properties, etc. of the application, and if you use them as, for example, a field name it will cause problems. Two very common examples are Name and Date. See this link for a more complete list; http://support.microsoft.com/kb/286335 4)Most developers use some sort of "naming convention" to help keep track of things as the application grows in size. Everyone has some of their own variations on naming conventions, but some things are common. For example; Table names commonly start with tbl Queries start with qry (or something similar) Forms start with frm, Sub forms start with fsub, sfrm, etc. Reports start with rpt A text box (on a form or report) would start with txt A combo box (same as above) would start with cbo etc. 5)Continuing with the naming theme. When you get to the point where you start designing forms and reports, you will notice that when you add a control (text box, combo box, etc.) to a form/report, Access will give will give it a completely useless name like "Text52" or "Combo38". Do yourself a favor and name them something meaningful like "txtEmployeePhone" or "cboSelectASubject". Good luck and post back when you have more questions. -- _________ Sean Bailey "Aria" wrote: First, let me say,"WOW"! You both have given me *so much* information. Thank you! My comments are below (I'm combining posts. I didn't know how else to do this): Bruce M: You could have a Titles table and an Employees table, with a EmployeeTitle junction table. That may make some sense if several employees have the same title, but if a lot of titles are unique that approach could become awkward. My thinking is that there would be a main form based on tblEmployees, with a subform based on the junction table. Aria: Do I need a subform if the majority of the staff only hold one title? Yes, I still trying to get over having empty fields but I haven't made it this far so I don't know. The form I envision would have this info on the main form using a combobox for the titles. I am trying to think of every possible title that I can. I don't want any suprises here. Bruce M: If you would like to do something such as generate reports that list employees by title, the junction table is probably the best way to go (or the most flexible, at least). Aria: That is exactly what I want to do; grouped by title. I think I'll do the junction table. Bruce M: You may do well to start a new thread on this specific topic. I'm not sure I have the experience to advise you confidently that one choice is better than another in a particular situation. Aria: I know I'm struggling but sometimes that's o.k. (I can't believe I said *that*!) Let me qualify that remark by saying it depends on the hour. I know I need to learn how to figure out some of these issues myself. I have to try first. If I continue to have problems, I will of course follow your advice (you and Beetle). Bruce M: The thing is that this database is about keys, but once you have an Employee table you will probably use it again and again (as a linked table in other projects), so it is well to design it as carefully as can be done. Aria: Absolutely!!! You know our history here (some bad memories). Keys are problematic for us. I have always said that I don't mind putting in the hard work at the beginning so that it will seem effortless in the end. Well, here it is. I am willing to put in the effort but some days I am frustrated beyond belief. I had to add a phone table because there are a possiblity of 3 phone #s that a staff member may have aside from the room phone#, which I moved to tblLocations per our previous discussion. I think this is a 1:M relationship. Bruce M: One employee: several phones is one-to-many, but if a phone could have several people answering it, depending on the time of day, you may need a jPhonePerson unction table. StartTime and EndTime may be fields in the junction table for the room phones. Ideally this would be linked to scheduling, so that a class schedule would indicate when an employee could be reached at a particular room phone (or something like that), but that could become quite complex. Aria: Whew! Thank goodness I don't have to worry about that. A room phone could have several people answer it. This will depend on who is assigned to the room. The good thing is that they don't roam. All bets are off for the upcoming school year though. I've heard rumors. I should be getting a room assignment list from the Principal soon. We'll see.... Aria: We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. Bruce M: If so, you could just store the department in tblEmployees. However, be sure of this, as it is moredifficult to rearrange the database later. Aria: Ugh! Nooooooo...I was so sure! I checked over and over and over again and last night I finally found it...one person, two departments. Will this never end?! "Beetle" wrote: One-to-One relationships are less common than 1:m and m:m, but in your case it may very well be applicable. They are typically used when you are sub-typing. The following is an excerpt from a recent post by resident guru Ken Sheridan that talks about this type of relationship (I couldn't possibly explain it any better). Hopefully he won't mind that I reposted his comments. The thread in question is about setting up a Church db, so you will see references to the Church of England etc., but the concept may be helpful in your situation. Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of information there. I had to really pay attention though and relate it to my situation. After reading it, I'm not as frustrated. Yes, there are 1:1 relationships here but it may be what I need. I do think that I have sub-types. Let me work with this some more before I let you both know which 1:1 relationships I have. A sub-type can of course have sub-types of its own; Chris Date in one of his books gives the example of a type Employees with sub-type programmers, and sub-types of programmers, System programmers and Application programmers. |
#28
|
|||
|
|||
Still Struggling...
I'm sorry for messing up the post. I think it added to the confusion.
1) Can an employee have more than one Classification, or can a Classification have more than one employee, or both? Maybe it would help if I explained the Classifications and what I'm thinking. The classifications a Administrator (Principal, Vice Principals) Certificated (anyone who holds a teaching credential such as Teachers, Counselors, possibly some other staffers) Classified (anyone who doesn't have a credential such as Custodians, Food Service, Public Safety Officers, Clerical, etc.) This is where I decided to add Substitutes. It seemed to me to be an attribute/sub-type of employee. True? Administrators can belong to both Certificated and Admin. but I don't even want to get into that. For my purposes, they are Administrators, period. That is of course unless you two present a situation where I will need to revise that (shudderplease don't...it's getting complicated). 2) Same as above but for Titles. You appear to have it set up as m:m but I just want to clarify. I set it up as a m:m because in a previous discussion, Bruce wanted me to be *sure* that one employee could only hold one title. I couldn't think of a situation where this wasn't true until I pulled our department list and saw that we do have some situations where one employee holds multiple titles (ex. Athlectic Director/Teacher, or Department Chair/Teacher, or Department Chair of 2 Departments/Teacher or co-Dept. chairs (you get the idea)). I"m drawing the line at Coach/Teacher. Why? Because Dept. Chair info is more important for our purposes than Coach. I can get that info elsewhere. 3) You have set up a m:m relationship between Departments and Subjects which I don't quite understand. For example, the Mathematics dept. would obviously have more than one Subject, but Trigonometry would only be part of one dept. (Mathematics). I must be missing something about what you are tracking here. O.k., now *I* don't understand. I thought this was related to the employee and whether they could teach more than one subject or belong to more than one dept.? The answer is yes, they can. Did I misunderstand? That's why I related it to tblSiteEmployee and not tblEmployees because this information does not pertain to Subs. Hmmm...I'm confused. Did I miss something here? I placed it in tblSiteEmps as FK to tblDeptSubjects. Obviously, I don't have the grasp on this that I thought I did. I've confused you both. 4) You appear to have no relationship between an employee and the dept. they work in or the subject they teach (at least not that I can see). What?! Please see above. I thought I had. I thought it should be in tblSiteEmp. I decided to use tblEmployees for *all* employees. Remember? You said in a previous post, whether they were permanent or temporary, they needed to be a part of the employee table (this is why tblSubs was deleted). So, to me, tblEmployees represents district employees (our school site employees plus any subs on campus for the day). This is the whole. The sub-type would be tblSiteEmp which includes only our sites permanent employees. I have fields in tblEmployees for Classification (Class) and Title. Then I could list for example Class: Substitute Title: Teacher or Custodian or whatever they happen to be. I admit I haven't resolved how I am going to work Preferred Subject or the Subs Credential (if they're Certificated) into the mix. I was thinking maybe I could just list it under Subject but that brought up other issues that was going to complicate things even more. Maybe I am trying to do to much, but, per our previous discussion, I really want to have a place for information about the Sub. It is critical to us; well, to me anyway, since I'm responsible for covering any absences for our campuses. I guarantee we are going to need that info. Right now, our system is my memory or if they gave me a business card, 98% of them don't have business cards. But, one thing at a time, I guess. "There are some other issues also..." Oh no...I was so happy when I finished and thought I had a good handle on things. You have a great weekend! -- Aria W. "Beetle" wrote: I have printed your post and will look it over, but I do have a few questions. 1) Can an employee have more than one Classification, or can a Classification have more than one employee, or both? 2) Same as above but for Titles. You appear to have it set up as m:m but I just want to clarify. 3) You have set up a m:m relationship between Departments and Subjects which I don't quite understand. For example, the Mathematics dept. would obviously have more than one Subject, but Trigonometry would only be part of one dept. (Mathematics). I must be missing something about what you are tracking here. 4) You appear to have no relationship between an employee and the dept. they work in or the subject they teach (at least not that I can see). There are some other issues also, but I'll cover those after I have a better understanding of the above questions. -- _________ Sean Bailey "Aria" wrote: I'm sorry. I messed it up. Sorry Bruce. Here is the revised version. Have a great weekend! tblEmployees ********** Inactive Yes/No EmpID (PK) Autonumber, long integer ClassID (FK to tblClassifications) EmpTitleID (FK to tblEmpTitles) PhoneID (FK to tblPhones) LN- txt FN-txt MI-txt (sfrm) tblSiteEmp (1:1) ************ SiteEmpID (PK) EmpID (FK) number, long integer DeptsSubjectsID (FK) number, long integer Address-txt City-txt State-txt Emergency Info(Hospital, Medications, Allergies etc.) DateEntered Date/Time DateModified Date/Time tblPhones (sfrm) 1:M *************** PhoneID (PK) Autonumber, long integer HomePhone-txt CellPhone-txt DistrictCell-txt DateEntered-Date/Time DateModified-Date/Time tblTitles 1:M ********* TitlesID (PK) Autonumber, long integer TitleName-txt tblSubjects 1:M *********** SubjectsID (PK) Autonumber, long integer SubjectName-txt tblDepts 1:M ********* DeptsID (PK) Autonumber, long integer DeptsName-txt tblDeptsSubjects M:M ************ DeptsSubjectsID (PK) Autonumber SubjectsID (FK to tblSubjects) DeptsID (FK to tblDepts) tblClassifications 1:M *************** ClassID (PK) Autonumber, long integer ClassName-txt tblTitlesEmps M:M ************* TitlesEmpsID (PK) Autonumber EmpID (FK to tblEmps) TitlesID (FK to tblTitles) *All FK keys are number, long integer Relationships: tblSiteEmp 1:1 tblEmps tblPhones 1:M tblEmps tblTitles 1:M tblTitlesEmps tblClassifications 1:M tblEmps tblTitlesEmps M:M tblEmps tblSubjects 1:M tblDeptsSubjects tblDepts 1:M tblDeptsSubjects tblDeptsSubjects M:M tblSiteEmp -- Aria W. "Beetle" wrote: I would echo what Bruce said and thank you for your kind reply. Here are a few additional thoughts of my own. Thank you for being here to answer all of my questions. I know my posts aren't the normal kind (a bazillion questions all at once). You have both been so kind and patient and have not yelled at me once (yes, I did have that fear when I posted). Happy to help. At least you want to learn and do things the right way. The posters we don't like are the ones that have already decided how they want to do things, even though it may be wrong, and expect someone here to explain to them how to do it the *wrong* way. Bothered by empty fields? Good. You should be. I know in a previous post I said something along the lines of "I might just live with a couple of empty fields in the Employees table", but I was just being lazy (shame on me!). Part of the goal of normalization is to eliminate redundancy and empty or "dead" fields. If an attribute doesn't apply to all employees, all the time, then it is an attribute of a sub-type, and therefore probably belongs in another table. Some other things to keep in mind for the future in the development cycle of your application; 1)Do not use "lookup fields" in your tables. This basically ampunts to a combo box, or list box, in a table. Combo/List boxes in forms are appropriate, but they should never be used in tables. See this link for more info; http://www.mvps.org/access/lookupfields.htm 2)Don't use spaces or other special characters in your table or field names. The same goes for queries, forms and reports (when you get to that point). Having spaces, etc. in the names will only cause you headaches later on. So instead of "Employees Table", it would more commonly be "tblEmployees". 3)Certain words are "reserved" in Access and should not be used as names of objects in your app. They are the names of different properties, etc. of the application, and if you use them as, for example, a field name it will cause problems. Two very common examples are Name and Date. See this link for a more complete list; http://support.microsoft.com/kb/286335 4)Most developers use some sort of "naming convention" to help keep track of things as the application grows in size. Everyone has some of their own variations on naming conventions, but some things are common. For example; Table names commonly start with tbl Queries start with qry (or something similar) Forms start with frm, Sub forms start with fsub, sfrm, etc. Reports start with rpt A text box (on a form or report) would start with txt A combo box (same as above) would start with cbo etc. 5)Continuing with the naming theme. When you get to the point where you start designing forms and reports, you will notice that when you add a control (text box, combo box, etc.) to a form/report, Access will give will give it a completely useless name like "Text52" or "Combo38". Do yourself a favor and name them something meaningful like "txtEmployeePhone" or "cboSelectASubject". Good luck and post back when you have more questions. -- _________ Sean Bailey "Aria" wrote: First, let me say,"WOW"! You both have given me *so much* information. Thank you! My comments are below (I'm combining posts. I didn't know how else to do this): Bruce M: You could have a Titles table and an Employees table, with a EmployeeTitle junction table. That may make some sense if several employees have the same title, but if a lot of titles are unique that approach could become awkward. My thinking is that there would be a main form based on tblEmployees, with a subform based on the junction table. Aria: Do I need a subform if the majority of the staff only hold one title? Yes, I still trying to get over having empty fields but I haven't made it this far so I don't know. The form I envision would have this info on the main form using a combobox for the titles. I am trying to think of every possible title that I can. I don't want any suprises here. Bruce M: If you would like to do something such as generate reports that list employees by title, the junction table is probably the best way to go (or the most flexible, at least). Aria: That is exactly what I want to do; grouped by title. I think I'll do the junction table. Bruce M: You may do well to start a new thread on this specific topic. I'm not sure I have the experience to advise you confidently that one choice is better than another in a particular situation. Aria: I know I'm struggling but sometimes that's o.k. (I can't believe I said *that*!) Let me qualify that remark by saying it depends on the hour. I know I need to learn how to figure out some of these issues myself. I have to try first. If I continue to have problems, I will of course follow your advice (you and Beetle). Bruce M: The thing is that this database is about keys, but once you have an Employee table you will probably use it again and again (as a linked table in other projects), so it is well to design it as carefully as can be done. Aria: Absolutely!!! You know our history here (some bad memories). Keys are problematic for us. I have always said that I don't mind putting in the hard work at the beginning so that it will seem effortless in the end. Well, here it is. I am willing to put in the effort but some days I am frustrated beyond belief. I had to add a phone table because there are a possiblity of 3 phone #s that a staff member may have aside from the room phone#, which I moved to tblLocations per our previous discussion. I think this is a 1:M relationship. Bruce M: One employee: several phones is one-to-many, but if a phone could have several people answering it, depending on the time of day, you may need a jPhonePerson unction table. StartTime and EndTime may be fields in the junction table for the room phones. Ideally this would be linked to scheduling, so that a class schedule would indicate when an employee could be reached at a particular room phone (or something like that), but that could become quite complex. Aria: Whew! Thank goodness I don't have to worry about that. A room phone could have several people answer it. This will depend on who is assigned to the room. The good thing is that they don't roam. All bets are off for the upcoming school year though. I've heard rumors. I should be getting a room assignment list from the Principal soon. We'll see.... Aria: We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. Bruce M: If so, you could just store the department in tblEmployees. However, be sure of this, as it is moredifficult to rearrange the database later. Aria: |
#29
|
|||
|
|||
Still Struggling...
OK, this post may get a bit long winded, so hopefully you won't pass
out from boredom before you get to the end. To start with, I would like to clarify some aspects of the relationship types. I will cover each type separately, using your data for examples. 1:1 *** You have a 1:1 relationship between tblEmployees and tblSiteEmp. In this case tblEmployees could be thought of as the "Parent" table and tblSiteEmp is the "Child" table. tblSiteEmp is the child because it is dependent upon tblEmployees. You can have an employee in tblEmployees without a related address in tblSiteEmp, but you cannot have an address in tblSiteEmp without a related employee in tblEmployees. The rules of this relationship also state that there can be only one child record for each parent record. Because of this, the child table (tblSiteEmp) does not need a separate primary key. EmpID would be the PK for both tables. In tblSiteEmp it acts as both the PK for that table and the FK to tblEmployees. In the parent table it can be an Autonumber but in the child table it cannot, because its value must be derived from an existing PK value in the parent table. When a new address record in entered in tblSiteEmp, an existing EmpID from tblEmployees is inserted into the EmpID field in tblSiteEmp. Now, when I say that the value is inserted, keep in mind that all data entry is done through forms, and that the form handles this process automatically. Your users would never even see the PK value, nor should they. In this case the tables might look like; tblEmployees ********** EmpID (Autonumber PK) LN FN MI tblSiteEmp ******** EmpID (PK/FK - long integer number) Address City State ZipCode 1:M *** For this example I am going to use tblEmployees and tblPhones, but with a couple of "disclaimers", so to speak. 1) There has been some back an forth in previous posts about whether a phone should be related to a Room or an Employee, as well as whether the relationship should be 1:M or M:M. I don't know enough details to answer either one of these questions, so this is only an example of how to set up a 1:M relationship. 2) I agree with Bruce in that an employees home phone and personal cell phone are part of their personal information, and therefore belong in tblEmployees, not tblPhones. IMO tblPhones should only store data about phones that are owned and maintained by your school, which may include cell phones *if* they are owned by the school and are assigned to employees. So in this relationship tblEmployees is again the parent, and tblPhones is the child. In this case we are allowed to have more than one child per parent, so the child table does need to have it's own PK. In this type of relationship the Foreign Key goes in the child table, or the table that is on the "many" side of the relationship. So EmpID goes in tblPhones as a FK, not the other way around (which is how you have it now). Another way of thinking about this is that a parent record does not necessarily have a child, but every child record must have a parent. Therefore, for every record in the child table, we need to know who the parent is. So the tables might look like; tblEmployees ********** EmpID (Autonumber PK) LN FN MI tblPhones ******* PhoneID (Autonumber PK) EmpID (FK to tblEmployees - long integer number) PhoneDescription PhoneNumber M:M *** You have more than one of this type, but I will use Employees and Titles for the example. As you know, this type of relationship needs a junction table. You can also look at this as two 1:M relationships where the junction table is the "many" side in both relationships. So you have; tblEmployees 1:M tblEmployeeTitles tblTitles 1:M tblEmployeeTitles So the combination of the two constitutes; tblEmployees M:M tblTitles Now, as I said previously, the "many" side table holds the foreign key. So, in this case, the junction table holds two FK's, which you have done correctly in your junction table. Where you went wrong is by putting EmpTitleID in tblEmployees as a FK. In fact, EmpTitleID does not need to exist in *either* table. In a junction table, it is the combination of the FK's that constitute the PK. In other words, each individual FK can be repeated many times, but for each record the *combination* of the FK's must be unique. The way you have it now, with EmpTitleID as the only unique identifier, there is nothing to prevent the same title being assigned to the same employee many times over. Now, you could leave EmpTitleID as the PK, and create a unique index on the two FK's, but that would be ignoring the fact that EmpTitleID is unnecessary. It is not good practice to introduce unnecessary elements into your db. To create the proper PK in this table, you would highlight both FK fields (EmpID and TitleID) in design view, and then set them as the PK. For this example, the table structure might look like; tblEmployees ********** EmpID (Autonumber PK) LN FN MI tblTitles ****** TitleID (Autonumber PK) TitleDescription tblEmpTitles ********* EmpID (Fk to tblEmployees and first part of PK - long integer number) TitleID (FK to tblTitles and second part of PK - long integer number) This same basic concept/structure should apply to your M:M relationship between Employees and Classifications also. Still awake?..........Hello?........ Pick your head up off that desk. Sleep on your own time, dammit! ;-) So now, let's move on from concepts to things that are more specific to your application. First, let's try to sort out the Department and Subject relationships. If all you were tracking was teachers this would be a bit simpler, because you would only need to relate them to Subjects. Since each Subject would belong to a Department, then you could determine what Departments a teacher is related to by virtue of the Subjects they teach. You would not need a direct relationship between the teacher and the department. However, that obviously will not work for you because not all of your employees teach a Subject but, presumably, they do all work in some Department. So essentially, as I see it anyway, you have two separate M:M relationships that you need to keep track of. Don't worry, it's not as complicated as it may sound at first. Right now you have tblDeptSubjects, which isn't quite right because in this case the relationship isn't between Departments and Subjects. Basically you have; tblEmployees M:M tblDepartments tblEmployees M:M tblSubjects So the junction tables would be tblEmpDepartments and tblEmpSubjects and the structure might look like; tblEmployees ********** (same fields as in the previous examples) tblDepartments *********** DeptID (Autonumber PK) DeptName tblSubjects ******** SubjectID (Autonumber PK) SubjectName tblEmpDepts ********* EmpID (FK to tblEmployees and first part of PK - long integer number) DeptID (FK to tblDepts and second part of PK - LI number) tblEmpSubjects *********** EmpID (FK to tblEmployees and first part of PK - LI number) SubjID (FK to tblSubjects and second part of PK - LI number) So in essence, your Subjects table has evolved. It is not just a way to track subjects that are taught. Rather, it is a way to describe a persons role within a Department. It would still include values that describe subjects like Economics, European History, and the like, but it would also have values like Department Chair and Custodial Staff. Moving on again, addressing some specific things from your last post; 4) You appear to have no relationship between an employee and the dept. they work in or the subject they teach. What?! Please see above. I thought I had. I thought it should be in tblSiteEmp. I didn't notice the field in tblSiteEmp at first. I can see why you thought it should go there. It's because you misunderstood where the FK's go. You thought they belonged in the "One" side table. You then probably thought if you put it in tblEmployees, you would end up with an empty field in the Substitute teacher records, so you put it in tblSiteEmp. This is actually wrong for two reasons; 1) As discussed before, the FK's don't go in the "One" side table 2) tblSiteEmp exists for one reason only. To store address information for your full time staff. Employee names are not in tblSiteEmp, so if you were to relate something to tblSiteEmp, you would essentially be relating it to an address, which would be somewhat meaningless without a name. I can see no scenario in which you would relate anything to tblSiteEmp (other than tblEmployees, which is its parent). I admit I haven't resolved how I am going to work Preferred Subject or the Subs Credential (if they're Certificated) into the mix. I was thinking maybe I could just list it under Subject but that brought up other issues that was going to complicate things even more. For Preferred Subject, just add a field to tblEmployees and have the users manually enter whatever the preferred subject is. This will add relatively little to the data entry process, and it will prevent you from having to add yet another relationship to your db. If you end up with a few records where the PreferredSubject field is empty, I think that's acceptable. As far as the Subs credentials, that would be handled in the same manner as the full time staff. If they have some type of credential, then there will be a record in tblEmpClassifications to reflect that fact. If not, there won't be a record. Simple. This is where I decided to add Substitutes. It seemed to me to be an attribute/sub-type of employee. True? Administrators can belong to both Certificated and Admin. but I don't even want to get into that. For my purposes, they are Administrators, period. That is of course unless you two present a situation where I will need to revise that (shudderplease don't...it's getting complicated). I agree. An Administrator is just that. Let's not add any more complication. Well, that's my two cents worth. Keep in mind that some of this is just my opinion. Someone else may disagree with some of what I've suggested because it is probably not *fully* normalized, but I think it's normalized enough that it would be completely functional. Once you get your head around how the relationships work, it should start to come into focus. Hopefully I didn't get carpel tunnel for nothing :-) BTW - Howcome I have to put in my two cents worth, but I only get a penny for my thoughts? g -- _________ Sean Bailey |
#30
|
|||
|
|||
Still Struggling...
I've seen your last post but have been having trouble posting back. If this
goes through I will respond later. -- Aria W. "Beetle" wrote: OK, this post may get a bit long winded, so hopefully you won't pass out from boredom before you get to the end. To start with, I would like to clarify some aspects of the relationship types. I will cover each type separately, using your data for examples. 1:1 *** You have a 1:1 relationship between tblEmployees and tblSiteEmp. In this case tblEmployees could be thought of as the "Parent" table and tblSiteEmp is the "Child" table. tblSiteEmp is the child because it is dependent upon tblEmployees. You can have an employee in tblEmployees without a related address in tblSiteEmp, but you cannot have an address in tblSiteEmp without a related employee in tblEmployees. The rules of this relationship also state that there can be only one child record for each parent record. Because of this, the child table (tblSiteEmp) does not need a separate primary key. EmpID would be the PK for both tables. In tblSiteEmp it acts as both the PK for that table and the FK to tblEmployees. In the parent table it can be an Autonumber but in the child table it cannot, because its value must be derived from an existing PK value in the parent table. When a new address record in entered in tblSiteEmp, an existing EmpID from tblEmployees is inserted into the EmpID field in tblSiteEmp. Now, when I say that the value is inserted, keep in mind that all data entry is done through forms, and that the form handles this process automatically. Your users would never even see the PK value, nor should they. In this case the tables might look like; tblEmployees ********** EmpID (Autonumber PK) LN FN MI tblSiteEmp ******** EmpID (PK/FK - long integer number) Address City State ZipCode 1:M *** For this example I am going to use tblEmployees and tblPhones, but with a couple of "disclaimers", so to speak. 1) There has been some back an forth in previous posts about whether a phone should be related to a Room or an Employee, as well as whether the relationship should be 1:M or M:M. I don't know enough details to answer either one of these questions, so this is only an example of how to set up a 1:M relationship. 2) I agree with Bruce in that an employees home phone and personal cell phone are part of their personal information, and therefore belong in tblEmployees, not tblPhones. IMO tblPhones should only store data about phones that are owned and maintained by your school, which may include cell phones *if* they are owned by the school and are assigned to employees. So in this relationship tblEmployees is again the parent, and tblPhones is the child. In this case we are allowed to have more than one child per parent, so the child table does need to have it's own PK. In this type of relationship the Foreign Key goes in the child table, or the table that is on the "many" side of the relationship. So EmpID goes in tblPhones as a FK, not the other way around (which is how you have it now). Another way of thinking about this is that a parent record does not necessarily have a child, but every child record must have a parent. Therefore, for every record in the child table, we need to know who the parent is. So the tables might look like; tblEmployees ********** EmpID (Autonumber PK) LN FN MI tblPhones ******* PhoneID (Autonumber PK) EmpID (FK to tblEmployees - long integer number) PhoneDescription PhoneNumber M:M *** You have more than one of this type, but I will use Employees and Titles for the example. As you know, this type of relationship needs a junction table. You can also look at this as two 1:M relationships where the junction table is the "many" side in both relationships. So you have; tblEmployees 1:M tblEmployeeTitles tblTitles 1:M tblEmployeeTitles So the combination of the two constitutes; tblEmployees M:M tblTitles Now, as I said previously, the "many" side table holds the foreign key. So, in this case, the junction table holds two FK's, which you have done correctly in your junction table. Where you went wrong is by putting EmpTitleID in tblEmployees as a FK. In fact, EmpTitleID does not need to exist in *either* table. In a junction table, it is the combination of the FK's that constitute the PK. In other words, each individual FK can be repeated many times, but for each record the *combination* of the FK's must be unique. The way you have it now, with EmpTitleID as the only unique identifier, there is nothing to prevent the same title being assigned to the same employee many times over. Now, you could leave EmpTitleID as the PK, and create a unique index on the two FK's, but that would be ignoring the fact that EmpTitleID is unnecessary. It is not good practice to introduce unnecessary elements into your db. To create the proper PK in this table, you would highlight both FK fields (EmpID and TitleID) in design view, and then set them as the PK. For this example, the table structure might look like; tblEmployees ********** EmpID (Autonumber PK) LN FN MI tblTitles ****** TitleID (Autonumber PK) TitleDescription tblEmpTitles ********* EmpID (Fk to tblEmployees and first part of PK - long integer number) TitleID (FK to tblTitles and second part of PK - long integer number) This same basic concept/structure should apply to your M:M relationship between Employees and Classifications also. Still awake?..........Hello?........ Pick your head up off that desk. Sleep on your own time, dammit! ;-) So now, let's move on from concepts to things that are more specific to your application. First, let's try to sort out the Department and Subject relationships. If all you were tracking was teachers this would be a bit simpler, because you would only need to relate them to Subjects. Since each Subject would belong to a Department, then you could determine what Departments a teacher is related to by virtue of the Subjects they teach. You would not need a direct relationship between the teacher and the department. However, that obviously will not work for you because not all of your employees teach a Subject but, presumably, they do all work in some Department. So essentially, as I see it anyway, you have two separate M:M relationships that you need to keep track of. Don't worry, it's not as complicated as it may sound at first. Right now you have tblDeptSubjects, which isn't quite right because in this case the relationship isn't between Departments and Subjects. Basically you have; tblEmployees M:M tblDepartments tblEmployees M:M tblSubjects So the junction tables would be tblEmpDepartments and tblEmpSubjects and the structure might look like; tblEmployees ********** (same fields as in the previous examples) tblDepartments *********** DeptID (Autonumber PK) DeptName tblSubjects ******** SubjectID (Autonumber PK) SubjectName tblEmpDepts ********* EmpID (FK to tblEmployees and first part of PK - long integer number) DeptID (FK to tblDepts and second part of PK - LI number) tblEmpSubjects *********** EmpID (FK to tblEmployees and first part of PK - LI number) SubjID (FK to tblSubjects and second part of PK - LI number) So in essence, your Subjects table has evolved. It is not just a way to track subjects that are taught. Rather, it is a way to describe a persons role within a Department. It would still include values that describe subjects like Economics, European History, and the like, but it would also have values like Department Chair and Custodial Staff. Moving on again, addressing some specific things from your last post; 4) You appear to have no relationship between an employee and the dept. they work in or the subject they teach. What?! Please see above. I thought I had. I thought it should be in tblSiteEmp. I didn't notice the field in tblSiteEmp at first. I can see why you thought it should go there. It's because you misunderstood where the FK's go. You thought they belonged in the "One" side table. You then probably thought if you put it in tblEmployees, you would end up with an empty field in the Substitute teacher records, so you put it in tblSiteEmp. This is actually wrong for two reasons; 1) As discussed before, the FK's don't go in the "One" side table 2) tblSiteEmp exists for one reason only. To store address information for your full time staff. Employee names are not in tblSiteEmp, so if you were to relate something to tblSiteEmp, you would essentially be relating it to an address, which would be somewhat meaningless without a name. I can see no scenario in which you would relate anything to tblSiteEmp (other than tblEmployees, which is its parent). I admit I haven't resolved how I am going to work Preferred Subject or the Subs Credential (if they're Certificated) into the mix. I was thinking maybe I could just list it under Subject but that brought up other issues that was going to complicate things even more. For Preferred Subject, just add a field to tblEmployees and have the users manually enter whatever the preferred subject is. This will add relatively little to the data entry process, and it will prevent you from having to add yet another relationship to your db. If you end up with a few records where the PreferredSubject field is empty, I think that's acceptable. As far as the Subs credentials, that would be handled in the same manner as the full time staff. If they have some type of credential, then there will be a record in tblEmpClassifications to reflect that fact. If not, there won't be a record. Simple. This is where I decided to add Substitutes. It seemed to me to be an attribute/sub-type of employee. True? Administrators can belong to both Certificated and Admin. but I don't even want to get into that. For my purposes, they are Administrators, period. That is of course unless you two present a situation where I will need to revise that (shudderplease don't...it's getting complicated). I agree. An Administrator is just that. Let's not add any more complication. Well, that's my two cents worth. Keep in mind that some of this is just my opinion. Someone else may disagree with some of what I've suggested because it is probably not *fully* normalized, but I think it's normalized enough that it would be completely functional. Once you get your head around how the relationships work, it should start to come into focus. Hopefully I didn't get carpel tunnel for nothing :-) BTW - Howcome I have to put in my two cents worth, but I only get a penny for my thoughts? g -- _________ Sean Bailey |
Thread Tools | |
Display Modes | |
|
|