Now I am totally clear on it! Took a few reiterations but I'm there. Even more I'm going to abandon the idea of titles. To implement it correctly, per the examples, you provided is an impossibility. At least something when weighed out doesn't mean enough to the effort it would take. Plus the lack it would still leave.
I'm going to go over my schema though and see where the many to many relationships will work well for me. I'm assuming telephone numbers (SL Green's example) that are specific to the user / member need not necessarily be broken out ? Perhaps they should be. Stuart --- Rhino <[EMAIL PROTECTED]> wrote: > Shawn, > > Yes, we *are* on the same page, now that I > understand that you didn't mean to implement the > many-to-many relationship in a single table. > > Stuart, > > Read what Stuart has described in this note. The > association table is the one he calls TItle_Member. > This table is *crucial* to the design, not just a > "nice-to-have". It gets one new row every time a > member gets an additional job and records the > member_id of the member and the title_id of the job. > This is the *heart* of the many-to-many > relationship; it's how you know which jobs are held > by which people and which people hold which jobs. > > With this table, you can get the member_id of every > person who has a particular job and the job_id of > every job ever done by a given member_id. If you > need to know the name of the person rather than > their member_id, you simply join to the Member table > using the member_id foreign key of Member_Title. By > the same token, if you need to know the job name, > you join to the Title table using the title_id. > Naturally, you do both joins if you want to know > both the job name and the member name. > > If you use this design, you should be able to store > any information you want very concisely and get back > anything you want to know very easily. Your primary > and foreign keys will make sense and will be easily > enforced. > > Is everything clear now? > > Believe me, the Systems community has a lot of > experience with the issues raised by many-to-many > relationships and this is the way we've been > handling those issues for many years now. > > Rhino > > ----- Original Message ----- > From: [EMAIL PROTECTED] > To: Rhino > Cc: [EMAIL PROTECTED] ; Stuart Felenstein > Sent: Friday, August 27, 2004 2:05 PM > Subject: Re: Many to Many: Does this make sense ? > > > > Rhino, > > You and I are on the same page. I also never seen > a "true many-to-many" relationship in database > design. The closest approximation I have seen is the > 3 table design like you and I have both proposed. > It's the only way I have ever seen to model a > many-to-many design, that's why I called it that. > Sorry for my semantic faux pas (You say po-tay-to, I > say po-tah-to..... ;-) > > As to the subject at hand, relational data > structures: > The "one-to-many" relationship. This is what > typically exists between items that are in a > parent-child relationship. Examples of this could be > a person and their phone numbers, a business and > it's employees, a discussion topic and its > responses. Notice how there is always one item > relating to several other items? Hence the name > one-to-many. Let's say you wanted a database that > can store peoples names and all of their phone > numbers. You could create one table with every > possible column for each type of phone this person > has. (I am leaving out the column type definitions > to save space) > > CREATE TABLE person ( > Name, > Title, > Address, > City, > State, > Zip, > HousePhone, > CellPhone, > FaxPhone, > OfficePhone, > SecretaryPhone, > GaragePhone, > DoctorPhone > ) > > But what happens when you need to add a new type > of phone number? You would be forced to change your > table design and possibly several sections of code. > This is a BAD design and should never happen. What > you need are two tables, one for personal > information, and one for phone numbers. > > CREATE TABLE person ( > ID > Name, > Title, > Address, > City, > State, > ZIP > ) > > CREATE TABLE PhoneNumber ( > person_ID, > Number, > Type > ) > > Each entry in the PhoneNumber table will equate a > person to a number and identify what type of phone > number it is. Can you see any reasonable limits to > how many different numbers you can store for each > person with this type of design?(of course there are > limits to how many records a database can hold be we > aren't talking about those). Where the single-table > model limited you to just a few, very particular, > phone numbers the two-table model allows you > complete flexibility. > > Let's examine your case of members and titles. > Basically you will have certain people with common > job titles (Database Analyst, Janitor, Receptionist, > etc.) and duplicating that information over and over > again in your database takes up lots of space but it > could fit into a two-table model. It would look > basically like this: > > CREATE TABLE member ( > ID, > Name, > ... other fields ... > ) > > CREATE TABLE title ( > member_ID, > JobTitle > ) > > To avoid storing the same JobTitle multiple time > in your title table, you would need to change your > design so that all titles are stored only once and > create an association table to link members to their > titles. > > CREATE TABLE member ( > ID, > Name, > ... other fields ... > ) > > CREATE TABLE title ( > ID, > JobTitle > ) > > CREATE TABLE title_member( > title_ID, > member_ID > ) > > This kind of design will allow each JobTitle to be > associated with multiple members and each member can > be associated with multiple JobTitles (many items of > one kind can associate with many items of another > kind, or "the items participate in a many-to-many > relationship"). Each association of a member to a > job title (or job title to a member, depending on > how you want to look at it) is an entry in the > title_member table. Here is a sample of how some > data might look. > > member title_member > title > +----+------+ +----------+-----------+ > +----+------------+ > | ID | Name | | title_id | member_id | > | ID | JobTitle | > +----+------+ +----------+-----------+ > +----+------------+ > | 1 | John | | 1 | 1 | > | 1 | Janitor | > | 2 | Mary | | 1 | 2 | > | 2 | Secretary | > | 3 | Sam | | 2 | 3 | > | 3 | Dog Walker | > | 4 | Jane | | 3 | 3 | > | 4 | Astronaut | > +----+------+ | 3 | 4 | > +----+------------+ > +----------+-----------+ > === message truncated === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
