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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Still Struggling...
Bruce,
A lock could have several keys. True. If a supply closet has three keys issued and a fourth person needs a key, you need to know that another key has to be made. *Very* true. The keys is an attribute of the lock, not the other way around. At least that's how I see it. I'm beginning to see what you mean here. If a lock is changed you need a way to find the existing keys so they can be swapped with the new ones. (stunned silence) You have my attention. This is exactly the situation we encountered this school year. Someone who had a master key was working at our site one weekend. The key was "lost". The decision was made to re-key an entire campus; every single room and door. The locksmith and I worked well together. It was a smooth process that took well over a month and a half to complete; but we both suffered abuse. This is not a situation I care to repeat. I see your point. Because our system is handwritten cards, I had no way of knowing who had what key unless I was given a name and could look it up. This was totally inadequate. I didn't mean to dismiss your idea out of hand, but I loathed the idea of adding another table to the database since I was having problems with the ones I currently have. So, should it look like this: tblLocks ****** LockID (PK) Autonumber KeyID (FK to tblKeys) RoomID (FK to tblRooms) Campus-text Wing-text RoomType-text If not, can you help me? I understand about I will need look-up tables. Not quite sure how all of this will fit together. I am going through all of the information that you and Beetle have given me. I want to say to you as I posted to him, my sincerest thanks for all of your help. To take on a novice and patiently give suggestions and explain your thinking process in a step by step manner, is not only appreciated but very special. -- Aria W. "BruceM" wrote: "Aria" wrote in message ... Bruce, First, I would really like to thank you for responding. I need to clarify some of the information that seems to be confusing. tblSubs is a table for temporary, roving district staff. They may be employed at any district site anywhere from a couple of hours to long-term(months). They need access to their assignment location and if they are certificated, temporary passwords for certain reports. My comments are as follows: 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 I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. I think I'm fairly comfortable with this table. I just thought maybe it needed to be broken down (not that I'm looking for another table to put in). Yes, the fields are seperate. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. Now, this is where you lose me. I don't understand the purpose of a Locks table. Isn't that what the keys table is for? We are only interested in the locks as far as what key will staff to gain access to the room, gate, stadium, etc. We don't normally track file cabinet keys. We may if the contents are extremely confidential. A lock could have several keys. If a supply closet has three keys issued and a fourth person needs a key, you need to know that another key has to be made. The keys is an attribute of the lock, not the other way around. At least that's how I see it. If a lock is changed you need a way to find the existing keys so they can be swapped with the new ones. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. Sorry; I dropped the ball on this one. This *is* long interger. I forgot to make the change in designation from my original. tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. Again, you lost me on this one. Why is the KeyID confusing? It relates to the room; knowing the key ID will allow you access. My thinking was that if I could do a parameter query (is that what I would want?) by room, I would know which key will unlock it. Then I could make a key request or whatever else needs to be done. As Beetle pointed out, RoomID is the PK of tblRooms (although again, I would identify Locks, which are not necessarily for rooms). A room (or lock) could have several keys, so KeyID is the FK to tblRooms (or tblLocks). 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 This table's purpose is unclear. I'm sorry. I should have explained in the beginning. Like I stated, this is for temporary, roving staff. We need Access only in relation to keys for this group. We have other applications for the rest of the information we need. Again, as Beetle suggested there is no need for this group to be separate from the people listed in tblEmployees (or tblPersonnel, if you prefer to look at it that way). 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. All the fields relating to school data wil be filled in, so I guess we're good here. Beetle made a good point about this if there somebody could have more than one subject, etc. I wish he had included more of the remarks when he replied. He posted that he was disagreeing with me (I think) about some things, but he did not include those things, so this thread is a bit fragmented. I have some other remarks in response to his reply. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. Oh my gosh! How many times have I read the same thing and because I *knew* my fields weren't like that, moved on. Well, I just wanted to double check and lo and behold, there it was; a # sign in my field name. Thanks for the heads up! One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and vice versa. However, if an employee has an office where only he or she answers the phone, the phone number is associated with the employee. Often in such case the phone number follows the employee if they move to another office. There is no single answer to your question. Your database can be made to accept either a phone associated with a room or with an employee, or both. It does start to become more complex if you have both situations. Well, for classroom staff, if they have to change rooms (happens every year, so I have no illusions here), they switch to the new rooms' phone #. For office staff, it could go either way. What do I need to do if anything? If a staff member has a fixed phone number, that should be part of their Employee record. If a room has a phone number, that is an attribute of the room, so you would start to get into having the phone number be associated with a person's schedule, which is an other project. Essentially you would need to have the person's schedule. If they are in room 101 from 10:00 - noon, from those hours you would need to contact them there. I'm not sure there is a simple answer to this question. 3. I forgot to include the following fields: Date assigned, Return Date and Permission to Retain (over the summer) for Emp. Keys. Should this be added to tblKeys or tblEmployees? I was told not to put *anything* into the junction table (tblKeysEmployees) besides the composite key already listed. You can have other fields in a junction table. For instance, the KeysEmployees junction table may include a DateIssued field, which is a property of neither the Employee nor the Key, but rather of this particular key together with this particular employee. If a key may be retained over the summer by one employee, but not another, then this too is a candidate for a field in the junction table. Your statements are true enough; only the master keys are coded to a particular employee. Classroom keys are a little more generic in that anyone who works out of that classroom or office will have the same key. O.k., I'll put the new fields here. 4.Keys Requests--Sometimes a request will not have a name associated with it. For instance, we need to request a key for the vault. I have not accounted for this situation and don't know how to handle it. If I need to order a sub. key, I can use the name of the permanent staff member associated with that room. What do I do about the vault? Should I enter "vault" in the emp. table w/o any additional data? Vault chould not be in the employee table, but the situation is unclear. Is nobody responsible for the vault key? How will you know who has it? How do you know now? Every key available on both campuses should be in the vault. Theoretically, vault keys remain in the vault. I guess I'm responsible for those keys but anyone who has the combination can remove a key without my knowledge. I guess I could list myself and under Remarks indicate it's a vault request. It sounds as if the vault contains the originals from which copies are made. If so, and if the vault has a combination lock, I'm not sure the vault fits into your schema. 5. Maybe I should cross this bridge when I get to it but I eventually will get to the point where I will make forms and subforms. There are approx. 400 seperate keys, not including multiple copies of the same key. If I have a combo box, is there a way to filter so that I am not looking at all 400 possiblities at once? Again, I'm not sure what you're asking. You will need forms and subforms as soon as you start doing data entry. It may be best to begin with just tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to work with the junction table arrangement. I'll hold off on this question and concentrate on getting the tables and fields correct. Bruce, again, I really appreciate all of your help. Thanks so much for talking me through it! -- Aria W. "BruceM" wrote: Responses inline. "Aria" wrote in message ... Hi, I'm still struggling to organize my tables and fields but I'm not as *totally confused* as my original post; at least I hope I'm not. I'm new to Access. This is my first database and I fluctuate between feeling |
#13
|
|||
|
|||
Still Struggling...
Huh? tblKeysEmployees? Not KeyID (FK to tblKeys)?
Yes tblKeysEmployees because that is the table that is used to track which employee has which key at a given time. If you put KeyID in tblSubs to assign keys to subs, then you have the same problem you had with tblEmployees. A sub will use more than one key, so you have to add additional records for the same sub. So you have to add another field in tblKeysEmployees as a foreign key to tblSubs. You can't use the existing EmployeeID FK because subs wouldn't exist in that table. So now you have at least one empty field for *every* record in tblKeysEmployees (either EmployeeID or SubID would be an empty field). That's what I mean when I say you end up with empty fields either way. However, there is a way to solve both problems, which I should have mentioned in my last post, but it involves - yes, that's right - ANOTHER TABLE...AAAAARRRRGGGGHHH! g You reduce tblEmployees to only those fields that apply to *all* personell (full time and subs), then you add another table for the data that applies to only *some* employees, and relate it back via EmployeeID. Serenity Now!g -- _________ Sean Bailey "Aria" wrote: 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 |
#14
|
|||
|
|||
Still Struggling...
Another table?! Scrap the whole thing; I'm going to do tblTours or
Products/Orders just like in the books! (lol) Ok, ok, it's a done deal! I'll change it as suggested. I like this idea much better. You have a fantastic day! I will be knee deep in all the posts that I printed out; rewriting my notes and diagramming my structure. -- Aria W. "Beetle" wrote: Huh? tblKeysEmployees? Not KeyID (FK to tblKeys)? Yes tblKeysEmployees because that is the table that is used to track which employee has which key at a given time. If you put KeyID in tblSubs to assign keys to subs, then you have the same problem you had with tblEmployees. A sub will use more than one key, so you have to add additional records for the same sub. So you have to add another field in tblKeysEmployees as a foreign key to tblSubs. You can't use the existing EmployeeID FK because subs wouldn't exist in that table. So now you have at least one empty field for *every* record in tblKeysEmployees (either EmployeeID or SubID would be an empty field). That's what I mean when I say you end up with empty fields either way. However, there is a way to solve both problems, which I should have mentioned in my last post, but it involves - yes, that's right - ANOTHER TABLE...AAAAARRRRGGGGHHH! g You reduce tblEmployees to only those fields that apply to *all* personell (full time and subs), then you add another table for the data that applies to only *some* employees, and relate it back via EmployeeID. Serenity Now!g -- _________ Sean Bailey "Aria" wrote: 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 |
#15
|
|||
|
|||
Still Struggling...
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. 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. "Aria" wrote in message ... Bruce, A lock could have several keys. True. If a supply closet has three keys issued and a fourth person needs a key, you need to know that another key has to be made. *Very* true. The keys is an attribute of the lock, not the other way around. At least that's how I see it. I'm beginning to see what you mean here. If a lock is changed you need a way to find the existing keys so they can be swapped with the new ones. (stunned silence) You have my attention. This is exactly the situation we encountered this school year. Someone who had a master key was working at our site one weekend. The key was "lost". The decision was made to re-key an entire campus; every single room and door. The locksmith and I worked well together. It was a smooth process that took well over a month and a half to complete; but we both suffered abuse. This is not a situation I care to repeat. I see your point. Because our system is handwritten cards, I had no way of knowing who had what key unless I was given a name and could look it up. This was totally inadequate. I didn't mean to dismiss your idea out of hand, but I loathed the idea of adding another table to the database since I was having problems with the ones I currently have. So, should it look like this: tblLocks ****** LockID (PK) Autonumber KeyID (FK to tblKeys) RoomID (FK to tblRooms) Campus-text Wing-text RoomType-text If not, can you help me? I understand about I will need look-up tables. Not quite sure how all of this will fit together. I am going through all of the information that you and Beetle have given me. I want to say to you as I posted to him, my sincerest thanks for all of your help. To take on a novice and patiently give suggestions and explain your thinking process in a step by step manner, is not only appreciated but very special. -- Aria W. "BruceM" wrote: "Aria" wrote in message ... Bruce, First, I would really like to thank you for responding. I need to clarify some of the information that seems to be confusing. tblSubs is a table for temporary, roving district staff. They may be employed at any district site anywhere from a couple of hours to long-term(months). They need access to their assignment location and if they are certificated, temporary passwords for certain reports. My comments are as follows: 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 I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. I think I'm fairly comfortable with this table. I just thought maybe it needed to be broken down (not that I'm looking for another table to put in). Yes, the fields are seperate. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. Now, this is where you lose me. I don't understand the purpose of a Locks table. Isn't that what the keys table is for? We are only interested in the locks as far as what key will staff to gain access to the room, gate, stadium, etc. We don't normally track file cabinet keys. We may if the contents are extremely confidential. A lock could have several keys. If a supply closet has three keys issued and a fourth person needs a key, you need to know that another key has to be made. The keys is an attribute of the lock, not the other way around. At least that's how I see it. If a lock is changed you need a way to find the existing keys so they can be swapped with the new ones. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. Sorry; I dropped the ball on this one. This *is* long interger. I forgot to make the change in designation from my original. tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. Again, you lost me on this one. Why is the KeyID confusing? It relates to the room; knowing the key ID will allow you access. My thinking was that if I could do a parameter query (is that what I would want?) by room, I would know which key will unlock it. Then I could make a key request or whatever else needs to be done. As Beetle pointed out, RoomID is the PK of tblRooms (although again, I would identify Locks, which are not necessarily for rooms). A room (or lock) could have several keys, so KeyID is the FK to tblRooms (or tblLocks). 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 This table's purpose is unclear. I'm sorry. I should have explained in the beginning. Like I stated, this is for temporary, roving staff. We need Access only in relation to keys for this group. We have other applications for the rest of the information we need. Again, as Beetle suggested there is no need for this group to be separate from the people listed in tblEmployees (or tblPersonnel, if you prefer to look at it that way). 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. All the fields relating to school data wil be filled in, so I guess we're good here. Beetle made a good point about this if there somebody could have more than one subject, etc. I wish he had included more of the remarks when he replied. He posted that he was disagreeing with me (I think) about some things, but he did not include those things, so this thread is a bit fragmented. I have some other remarks in response to his reply. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. Oh my gosh! How many times have I read the same thing and because I *knew* my fields weren't like that, moved on. Well, I just wanted to double check and lo and behold, there it was; a # sign in my field name. Thanks for the heads up! One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and vice versa. However, if an employee has an office where only he or she answers the phone, the phone number is associated with the employee. Often in such case the phone number follows the employee if they move to another office. There is no single answer to your question. Your database can be made to accept either a phone associated with a room or with an employee, or both. It does start to become more complex if you have both situations. Well, for classroom staff, if they have to change rooms (happens every year, so I have no illusions here), they switch to the new rooms' phone #. For office staff, it could go either way. What do I need to do if anything? If a staff member has a fixed phone number, that should be part of their Employee record. If a room has a phone number, that is an attribute of the room, so you would start to get into having the phone number be associated with a person's schedule, which is an other project. Essentially you would need to have the person's schedule. If they are in room 101 from 10:00 - noon, from those hours you would need to contact them there. I'm not sure there is a simple answer to this question. 3. I forgot to include the following fields: Date assigned, Return Date and Permission to Retain (over the summer) for Emp. Keys. Should this be added to tblKeys or tblEmployees? I was told not to put *anything* into the junction table (tblKeysEmployees) besides the composite key already listed. You can have other fields in a junction table. For instance, the KeysEmployees junction table may include a DateIssued field, which is a property of neither the Employee nor the Key, but rather of this particular key together with this particular employee. If a key may be retained over the summer by one employee, but not another, then this too is a candidate for a field in the junction table. Your statements are true enough; only the master keys are coded to a particular employee. Classroom keys are a little more generic in that anyone who works out of that classroom or office will have the same key. O.k., I'll put the new fields here. 4.Keys Requests--Sometimes a request will not have a name associated with it. For instance, we need to request a key for the vault. I have not accounted for this situation and don't know how to handle it. If I need to order a sub. key, I can use the name of the permanent staff member associated with that room. What do I do about the vault? Should I enter "vault" in the emp. table w/o any additional data? Vault chould not be in the employee table, but the situation is unclear. Is nobody responsible for the vault key? How will you know who has it? How do you know now? Every key available on both campuses should be in the vault. Theoretically, vault keys remain in the vault. I guess I'm responsible for those keys but anyone who has the combination can remove a key without my knowledge. I guess I could list myself and under Remarks indicate it's a vault request. It sounds as if the vault contains the originals from which copies are made. If so, and if the vault has a combination lock, I'm not sure the vault fits into your schema. 5. Maybe I should cross this bridge when I get to it but I eventually will get to the point where I will make forms and subforms. There are approx. 400 seperate keys, not including multiple copies of the same key. If I have a combo box, is there a way to filter so that I am not looking at all 400 possiblities at once? Again, I'm not sure what you're asking. You will need forms and subforms as soon as you start doing data entry. It may be best to begin with just tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to work with the junction table arrangement. I'll hold off on this question and concentrate on getting the tables and fields correct. Bruce, again, I really appreciate all of your help. Thanks so much for talking me through it! -- Aria W. "BruceM" wrote: Responses inline. "Aria" wrote in message ... Hi, I'm still struggling to organize my tables and fields but I'm not as *totally confused* as my original post; at least I hope I'm not. I'm new to Access. This is my first database and I fluctuate between feeling |
#16
|
|||
|
|||
Still Struggling...
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 straightforward in the beginning but now...additional issues keep popping up. Before yesterday, I would have said that staff can only hold a single title. I have thought of several situations where this is not true. If I have this right, it would be a many-to-many relationship and requires a junction table. Let me know if I've stepped off the cliff here. 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. We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. 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. -- Aria W. "Beetle" wrote: Huh? tblKeysEmployees? Not KeyID (FK to tblKeys)? Yes tblKeysEmployees because that is the table that is used to track which employee has which key at a given time. If you put KeyID in tblSubs to assign keys to subs, then you have the same problem you had with tblEmployees. A sub will use more than one key, so you have to add additional records for the same sub. So you have to add another field in tblKeysEmployees as a foreign key to tblSubs. You can't use the existing EmployeeID FK because subs wouldn't exist in that table. So now you have at least one empty field for *every* record in tblKeysEmployees (either EmployeeID or SubID would be an empty field). That's what I mean when I say you end up with empty fields either way. However, there is a way to solve both problems, which I should have mentioned in my last post, but it involves - yes, that's right - ANOTHER TABLE...AAAAARRRRGGGGHHH! g You reduce tblEmployees to only those fields that apply to *all* personell (full time and subs), then you add another table for the data that applies to only *some* employees, and relate it back via EmployeeID. Serenity Now!g -- _________ Sean Bailey "Aria" wrote: 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 |
#17
|
|||
|
|||
Still Struggling...
You're doing fine with newsgroup etiquette and all that. I suggested a
single thread only because it may be easier for all involved. More comments inline. "Aria" wrote in message ... 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 straightforward in the beginning but now...additional issues keep popping up. Before yesterday, I would have said that staff can only hold a single title. I have thought of several situations where this is not true. If I have this right, it would be a many-to-many relationship and requires a junction table. Let me know if I've stepped off the cliff here. 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. The subform would have a combo box based on tblTitles. However, if a title is not in tblTitles it would need to be added before it can be selected from the combo box. That process can be made fairly efficient and simple to perform, but if there is a frequent need to add a title to tblTitles (because most titles are unique) it could become tedious. Another approach would be that tblEmployeeTitle not be a junction table, but rather that it be related one to many from tblEmployees (one employee: several titles). Base a combo box on tblTitles, and store the actual title in tblEmployeeTitle. Set Limit To List to No for the combo box, and type in a title if it is not in tblTitles (or use the Not In List event to add it to tblTitles). Again, it depends on the situation. 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). 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. 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. 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. 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. We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. If so, you could just store the department in tblEmployees. However, be sure of this, as it is moredifficult to rearrange the database later. 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. What 1:1 relationships? There are valid reasons for using these, but they could also just serve to make things complicated. -- Aria W. "Beetle" wrote: Huh? tblKeysEmployees? Not KeyID (FK to tblKeys)? Yes tblKeysEmployees because that is the table that is used to track which employee has which key at a given time. If you put KeyID in tblSubs to assign keys to subs, then you have the same problem you had with tblEmployees. A sub will use more than one key, so you have to add additional records for the same sub. So you have to add another field in tblKeysEmployees as a foreign key to tblSubs. You can't use the existing EmployeeID FK because subs wouldn't exist in that table. So now you have at least one empty field for *every* record in tblKeysEmployees (either EmployeeID or SubID would be an empty field). That's what I mean when I say you end up with empty fields either way. However, there is a way to solve both problems, which I should have mentioned in my last post, but it involves - yes, that's right - ANOTHER TABLE...AAAAARRRRGGGGHHH! g You reduce tblEmployees to only those fields that apply to *all* personell (full time and subs), then you add another table for the data that applies to only *some* employees, and relate it back via EmployeeID. Serenity Now!g -- _________ Sean Bailey "Aria" wrote: 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 |
#18
|
|||
|
|||
Still Struggling...
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 straightforward in the beginning but now...additional issues keep popping up. Before yesterday, I would have said that staff can only hold a single title. I have thought of several situations where this is not true. If I have this right, it would be a many-to-many relationship and requires a junction table. Let me know if I've stepped off the cliff here. 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. We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. 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. -- Aria W. "Beetle" wrote: Huh? tblKeysEmployees? Not KeyID (FK to tblKeys)? Yes tblKeysEmployees because that is the table that is used to track which employee has which key at a given time. If you put KeyID in tblSubs to assign keys to subs, then you have the same problem you had with tblEmployees. A sub will use more than one key, so you have to add additional records for the same sub. So you have to add another field in tblKeysEmployees as a foreign key to tblSubs. You can't use the existing EmployeeID FK because subs wouldn't exist in that table. So now you have at least one empty field for *every* record in tblKeysEmployees (either EmployeeID or SubID would be an empty field). That's what I mean when I say you end up with empty fields either way. However, there is a way to solve both problems, which I should have mentioned in my last post, but it involves - yes, that's right - ANOTHER TABLE...AAAAARRRRGGGGHHH! g You reduce tblEmployees to only those fields that apply to *all* personell (full time and subs), then you add another table for the data that applies to only *some* employees, and relate it back via EmployeeID. Serenity Now!g -- _________ Sean Bailey "Aria" wrote: 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. -- |
#19
|
|||
|
|||
Still Struggling...
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 straightforward in the beginning but now...additional issues keep popping up. Before yesterday, I would have said that staff can only hold a single title. I have thought of several situations where this is not true. If I have this right, it would be a many-to-many relationship and requires a junction table. Let me know if I've stepped off the cliff here. 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. We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. 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. -- Aria W. "Beetle" wrote: Huh? tblKeysEmployees? Not KeyID (FK to tblKeys)? Yes tblKeysEmployees because that is the table that is used to track which employee has which key at a given time. If you put KeyID in tblSubs to assign keys to subs, then you have the same problem you had with tblEmployees. A sub will use more than one key, so you have to add additional records for the same sub. So you have to add another field in tblKeysEmployees as a foreign key to tblSubs. You can't use the existing EmployeeID FK because subs wouldn't exist in that table. So now you have at least one empty field for *every* record in tblKeysEmployees (either EmployeeID or SubID would be an empty field). That's what I mean when I say you end up with empty fields either way. However, there is a way to solve both problems, which I should have mentioned in my last post, but it involves - yes, that's right - ANOTHER TABLE...AAAAARRRRGGGGHHH! g You reduce tblEmployees to only those fields that apply to *all* personell (full time and subs), then you add another table for the data that applies to only *some* employees, and relate it back via EmployeeID. Serenity Now!g -- _________ Sean Bailey "Aria" wrote: 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, |
#20
|
|||
|
|||
Still Struggling...
What a wonderfully gracious reply. Thank you for your kind words.
I know you said you are going to work on this for a few days, but here are a couple of thoughts anyhow. 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, which would be a simpler task than trying to tie in the phone numbers with a schedule, particularly since you aren't using the dtabase for scheduling. A sort of standard example of a one-to-one relationship is a company softball team. A SoftballTeam table may contain information that is not in the main Employee record, so it is set apart in its own table. An employee would only be in the SoftballTeam table once, and likewise an employee on the softball team will have just one corresponding record in the main Employee table. "Aria" wrote in message ... 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 straightforward in the beginning but now...additional issues keep popping up. Before yesterday, I would have said that staff can only hold a single title. I have thought of several situations where this is not true. If I have this right, it would be a many-to-many relationship and requires a junction table. Let me know if I've stepped off the cliff here. 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. We also discussed department affiliation but I can't recall an instance where staff will belong to more than one department. 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. -- Aria W. "Beetle" wrote: Huh? tblKeysEmployees? Not KeyID (FK to tblKeys)? Yes tblKeysEmployees because that is the table that is used to track which employee has which key at a given time. If you put KeyID in tblSubs to assign keys to subs, then you have the same problem you had with tblEmployees. A sub will use more than one key, so you have to add additional records for the same sub. So you have to add another field in tblKeysEmployees as a foreign key to tblSubs. You can't use the existing EmployeeID FK because subs wouldn't exist in that table. So now you have at least one empty field for *every* record in tblKeysEmployees (either EmployeeID or SubID would be an empty field). That's what I mean when I say you end up with empty fields either way. However, there is a way to solve both problems, which I should have mentioned in my last post, but it involves - yes, that's right - ANOTHER TABLE...AAAAARRRRGGGGHHH! g You reduce tblEmployees to only those fields that apply to *all* personell (full time and subs), then you add another table for the data that applies to only *some* employees, and relate it back via EmployeeID. Serenity Now!g -- _________ Sean Bailey "Aria" wrote: 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, |
Thread Tools | |
Display Modes | |
|
|