A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Many-2-many relationships: Can I be told ...



 
 
Thread Tools Display Modes
  #41  
Old November 10th, 2006, 08:51 PM posted to microsoft.public.access.tablesdbdesign
scubadiver
external usenet poster
 
Posts: 1,673
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.