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 |
#41
|
|||
|
|||
Many-2-many relationships: Can I be told ...
So it isn't the data entry, it is the fact that you can change one record
instead of many. Now that you have explained it that way, it is a lot clearer to me. It didn't get through to me at first, thanks. That is the answer I *think* I was looking for to my original question ... I say *think* because, in my current design, course type and course name will be taken from look up tables. Couple of points: Instructor name *is* independent of course name and is taken from a query based on whether an employee is also an instructor (but it also excludes the current main record since an employee can't instruct themselves LoL). Based on that, it could be helpful to have an extra 1:n relationship between course and the valid list of instructors. what do you think? The second point is that training may not have a regular timetable (like some major companies that have external training courses). I have designed another database which should have a m:m relationship but doesn't. I will redo the tables because I have information. thanks! "John Vinson" wrote: On Fri, 10 Nov 2006 09:12:01 -0800, scubadiver wrote: tble_employee EmployeeID (PK) EmplName workstatus operations Dept Subdept Supervisor tble_course employeeID coursetype coursename trainingdate instructor I can see this is going nowhere because I still haven't been given a practical answer as to why a m:m relationship is any better. Maybe it is a lot simpler than I think it is but I can't see why. To give an example, in "ImplementingM2MRelationship.mdb" one of the forms consists of a main form (department) and a subform (transaction info and item info). The fields are from two different tables, so why is this necessary? I could be entering the same items for different departments? How is that different to having 1:m relationship? The problem with your design is that if a given course has 24 employees registered, you must have 24 records in tble_course for that course. They all will have the same coursetype, coursename, trainingdate, and instructor. This wastes disk space, of course, since there is really only ONE course; with a many to many, you would enter these fields once and once only. The *real* problem though is that if you have 24 records, there is no way to be sure that the all DO in fact have the same coursename, trainingdate, and instructor. You could have a data entry error (typing the course date 24 times, wasting the data entry person's time) where there's a typo, and you have 23 employees coming on 11/10 and one showing up on 11/11 wondering why there's nobody there. Or your instructor might quit and need to be replaced; now you must track down 24 records to change the instructor, whereas with the many to many you make the change ONCE, in ONE record. If you really want to store information redundantly, you can. You're wasting storage, making your database less efficient, and - much worse - risking the integrity of your data. If that cost is irrelevant to you, go right ahead! John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|