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 |
#8
|
|||
|
|||
Still Struggling...
I have read and reread your comments and suggestions. I am commenting on the
following: Does school data need an additional table based solely on instuctors teaching more than one subject? You are correct though. Teachers can teach more than one subject. I only ask because you said, "additionally", which implies that this will need a new table regardless. Other than "Subject", I don't understand how I would be entering the same information again and again. What am I missing? In my opinion it should be in separate tables regardless. I don't know how Classification and Title relate to your employees, so for now I'll use Dept. Name and Subject as examples. As your table is currently designed, each time you enter an employee record the user will have to manually type in the Dept. Name and Subject. This is not only extra work, it also invites spelling errors and invalid data in your table. ....and the light goes on. Of course! How could I have missed *that*?! Which tables should DeptID/SubjectID go in? Good question. I think this is a very important part of your application that you have not completely defined yet. In a relational database like Access, it is not only imerative that you determine what relationships you have, but what *type* each relationship is. I follow you. That was a great explanation. I understand and will be thinking about this further. A Campus would usually encompass more than one building, a building may have more than one wing, and a wing may have more than one room. So, again, if we break it down to LCD (so to speak) a room is only indirectly related to a Campus, it's direct relationship would the wing in which it is located (or perhaps the building if that building has no "wings"). However, as Bruce correctly pointed out, what we are really talking about is locks, so it should probably be broken down to that level. Again, I follow what you're saying and having had time to think about all of the information that both you and Bruce pointed out, I agree. He is correct. He also threw in a monkey wrench. I did not want to hear about a Locks table when I was having trouble with the tables I already have. But he posted something in his reply that just knocked the wind out of me, but it opened my eyes to see what he was saying. I hadn't thought about Master keys. You may want to consider a separate table for those, since they have a different type of relationship with the locks than the regular keys. MasterKeys MasterID EmpID KeyID (FK to tblKeys)? I don't understand. If I add the substitute staff to the our employee table, most of the fields will be empty. OK, I can see where that is a little tricky. Still, I think I would just list them in the employee table and live with a few empty address fields. If you have them in a separate table, then you're going to have to add another FK field to your tblKeysEmployees, so either way you end up with empty fields in one of your tables. Huh? tblKeysEmployees? Not KeyID (FK to tblKeys)? You know I don't know what it is about this table that makes me want to keep it. I respect your opinion and will probably rue the day that I didn't follow your advice; especially if I have to post back and have someone extricate me from my own dumb mistake (be kind if that happens!). After all, I came here for advice. I think it's all of the empty fields that I'm going to have. They bother me. They won't need the inactive field or the "Subject", "Address", or all of the Emergency Contact info (Hospital, Emergency Contact, Medications, Allergies, etc.) I'm still thinking about this one. Again, my sincerest thanks for your help. This has been eye-opening. Both you and Bruce have shown me how to think about some of these problems in a different way. You both pan wide and then zoom in on the tiny details that are easily overlooked by novices. It's easy to answer the cut and dry questions. But someone who takes on a rock bottom beginner and walks you step by step through the process...well, that's extra special. -- Aria W. "Beetle" wrote: Does school data need an additional table based solely on instuctors teaching more than one subject? You are correct though. Teachers can teach more than one subject. I only ask because you said, "additionally", which implies that this will need a new table regardless. Other than "Subject", I don't understand how I would be entering the same information again and again. What am I missing? In my opinion it should be in separate tables regardless. I don't know how Classification and Title relate to your employees, so for now I'll use Dept. Name and Subject as examples. As your table is currently designed, each time you enter an employee record the user will have to manually type in the Dept. Name and Subject. This is not only extra work, it also invites spelling errors and invalid data in your table. The correct way is to have separate "lookup" tables that store all possible Departments and Subjects. These would be very simple tables that would likely just have a few fields like; DeptID DeptName SubjectID SubjectName Then you would use DeptID and SubjectID as foreign keys in other tables. In your data entry forms, you would typically use combo boxes to allow users to select the correct Dept./Subject from the list of choices. Which tables should DeptID/SubjectID go in? Good question. I think this is a very important part of your application that you have not completely defined yet. In a relational database like Access, it is not only imerative that you determine what relationships you have, but what *type* each relationship is. Is it One-to-Many? Is it Many-to-Many? If it is 1:m, then the PK field from the "One" side table goes in the "Many" side table as a foreign key. If it is m:m, then you need a third (junction) table to define the relationship, like your tblKeysEmployees. You said a teacher can teach more than one subject, and, presumably, a subject can be taught by more than one teacher, so this relationship is m:m. Therefore, you not only need another table to define the list of available subjects, but also a junction table to define the relationship. Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings Point taken; although I don't understand why the campus location is not a room attribute. I need to know where that room or storage room is located(especially since storage rooms don't have room #s). Does it make a difference if there are only 2 campuses? No? So, is it CampusID (FK to tblRooms)? Same for tblWings? Also, I meant to put KeyType (Storage, Classroom, Gate Master, Grand Master, etc.) as a field here. As I stated in my reply to Bruce, I don't think I worded this part of my reply very well. I was trying to point out that things like RoomType are not attributes of a key. I actually think bruce is right about this. When you break things down to lowest common denominator, all a key does is open a lock (or many locks if it is a master key). Where that lock is located is an attribute of the lock, not the key. As far as whether the Campus is an attribute of the Room? Maybe it should be, I don't know enough about your app to say for sure. My train of thought in my previous reply wasa basically the following; A Campus would usually encompass more than one building, a building may have more than one wing, and a wing may have more than one room. So, again, if we break it down to LCD (so to speak) a room is only indirectly related to a Campus, it's direct relationship would the wing in which it is located (or perhaps the building if that building has no "wings"). However, as Bruce correctly pointed out, what we are really talking about is locks, so it should probably be broken down to that level. Good point; I'll change this. Your second sentence is not exactly true. Just to clarify, key assignments are based on job title and extracurricular duties (coaching, tutoring, special projects, etc.). Masters allow all access per location. So one key may open one or many doors. I hadn't thought about Master keys. You may want to consider a separate table for those, since they have a different type of relationship with the locks than the regular keys. I don't understand. If I add the substitute staff to the our employee table, most of the fields will be empty. They are not required to disclose their address or emergency info. There address is on file with the district. In addition, they are not bound to teach any subject in particular. If there is a request for a P.E. instructor but they normally teach English, they are free to accept the position for the time requested. This is why I am adding a "Preferred Subject" field. I want the sub to enjoy there time teaching and the permanent staff to feel comfortable with whomever is taking over their class. I am often asked for recommendations; I'd like some info to go along with that. If we have a long-term position for a Special Education instructor, I need to know who has that credential so we can move ASAP. Oddly enough, the district doesn't track this info; I've asked. If I have the subs in their own table, I think all the info I need will be together. OK, I can see where that is a little tricky. Still, I think I would just list them in the employee table and live with a few empty address fields. If you have them in a separate table, then you're going to have to add another FK field to your tblKeysEmployees, so either way you end up with empty fields in one of your tables. Thank you for saying that! I kept wondering,"Why am I having such a hard time with this?" Why am I still on the pen, paper and diagram stage? I like a challenge because it makes success that much sweeter but *come on*...! It makes it difficult to make a move when you are about ready to step off the cliff at every turn. If it weren't for this discussion group, I would have. Thanks to both you and Bruce for posting your reasons and thought process along with your comments. I don't know about others, but it helps me to follow along. I t takes an extra measure of patience that I appreciate. I am going to rethink my tables and post them again. I hope you will take a look and tell me what you think. Yeah, you kind of jumped right into the deep end of the pool, but hopefully with help from the group here you'll get it all sorted out. You'll get differing opinions from people too, so sometimes you just have to absorb the different ideas and then decide what you think will work best for you. -- _________ Sean Bailey "Aria" wrote: Beetle, (lol) Yes, you have definitely thrown a monkey wrench into the works! You have raised some valid points that I need to take into account. I have thought about what you posted and would like to clarify some points and ask additional questions. tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. Does school data need an additional table based solely on instuctors teaching more than one subject? You are correct though. Teachers can teach more than one subject. I only ask because you said, "additionally", which implies that this will need a new table regardless. Other than "Subject", I don't understand how I would be entering the same information again and again. What am I missing? tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings Point taken; although I don't understand why the campus location is not a room attribute. I need to know where that room or storage room is located(especially since storage rooms don't have room #s). Does it make a difference if there are only 2 campuses? No? So, is it CampusID (FK to tblRooms)? Same for tblWings? Also, I meant to put KeyType (Storage, Classroom, Gate Master, Grand Master, etc.) as a field here. tblRooms Key ID (PK)-text Room number- text Remarks-text This table should have RoomID as a PK (KeyID does not belong here). A room can have many keys, but a key can only open one room, so RoomID goes in tblKeys as a foreign key, not the other way around. Good point; I'll change this. Your second sentence is not exactly true. Just to clarify, key assignments are based on job title and extracurricular duties (coaching, tutoring, special projects, etc.). Masters allow all access per location. So one key may open one or many doors. As I stated previously, you are correct about the room type and wing being attributes of the room. Why not "Campus"? In addition, the stadium and storage rooms do not have a room #, nor does it belong to a wing. Do I need additional tables for this? tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time IMO this table is unecessary. Whether an employee is "full time" or "temporary" is just an attribute that would go in tblEmployees. The fact that a person may come and go as a member of your staff is irrelevant to the fact that that person was in posession of a certain key at a certain time. The date that a key was issued/returned belongs in tblKeysEmployees. I don't understand. If I add the substitute staff to the our employee table, most of the fields will be empty. They are not required to disclose their address or emergency info. There address is on file with the district. In addition, they are not bound to teach any subject in particular. If there is a request for a P.E. instructor but they normally teach English, they are free to accept the position for the time requested. This is why I am adding a "Preferred Subject" field. I want the sub to enjoy there time teaching and the permanent staff to feel comfortable with whomever is taking over their class. I am often asked for recommendations; I'd like some info to go along with that. If we have a long-term position for a Special Education instructor, I need to know who has that credential so we can move ASAP. Oddly enough, the district doesn't track this info; I've asked. If I have the subs in their own table, I think all the info I need will be together. You might be thinking "great, now I have to add more tables" but keep in mind that in an application like this you will typically have several tables that will basically just be "lookup" tables. They aren't really a big deal to set up. (lol) That is *exactly* what I was thinking! You may also need some additional junction tables, depending on if - as I stated earlier - an instructor can teach more than one subject, work in more than one department, etc. (heavy sigh...as my head hits the desk) Which ones? tblSubjects? For you first app, you didn't exactly pick a simple one, but everyone loves a challenge, right? g Thank you for saying that! I kept wondering,"Why am I having such a hard time with this?" Why am I still on the pen, paper and diagram stage? I like a challenge because it makes success that much sweeter but *come on*...! It makes it difficult to make a move when you are about ready to step off the cliff at every turn. If it weren't for this discussion group, I would have. Thanks to both you and Bruce for posting your reasons and thought process along with your comments. I don't know about others, but it helps me to follow along. I t takes an extra measure of patience that I appreciate. I am going to rethink my tables and post them again. I hope you will take a look and tell me what you think. -- Aria W. "Beetle" wrote: Hate to throw in the monkey wrench, but I'm going to have to disagree with some of what's been posted (comments inline). tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll |
Thread Tools | |
Display Modes | |
|
|