My replies are interspersed below. Rhino
----- Original Message ----- From: "Stuart Felenstein" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; "Stuart Felenstein" <[EMAIL PROTECTED]> Sent: Friday, August 27, 2004 3:05 PM Subject: Re: Many to Many: Does this make sense ? > 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. I was breathing a sigh of relief that we'd finally articulated the idea clearly enough. But now I'm not so sure.... What do you mean you are 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. > Huh? I don't think we proved that it was impossible to implement titles. In fact, I think we proved the exact opposite, that the right way to implement them was with association tables. Maybe you should show us your revised database design before you try to implement it, just to be sure that you aren't missing something important. > 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. > I think Shawn's example (correct me if I'm wrong, Shawn) was meant to show that if you have a variable number of types of phone numbers and new types of phone numbers appearing regularly, it would make more sense to store them as he showed than to keep adding new phone number columns to the member table. When I was a kid, back in the early mists of time, most people had one and only one phone number. (There were still even people that had no phones at all.) The phone, if you had one, probably sat in your kitchen. Very few people even had extension phones and it was almost unheard of for people to have a second phone line in the house; it might happen if the house was both a residence and the site of a small business. In those days, it was pretty rare for people to call you at the office about something that had to do with your personal life; for example, if your cheque to the hydro company was overdue, they'd call you at home. So, if you wanted to record information about people in a database, you'd probably just record their home phone number. Today, things have changed a great deal. Now it is very common for a home phone to have many extensions. Many people have two or more phone lines; one might be for faxes, the other for voice calls and DSL. Many people have cell phones so that you can reach them when they are out. Many companies are far more tolerant of their employees discussing personal matters on company time. Many people have pagers, Blackberries, etc. So what information would you store for a person if you were building a database today? Well, depending on how urgently you wanted to talk to them, you might store every number they had. If you are building an employee database and one particular employee was utterly critical to your operations, you might want to store his home phone number, his cell number, his work number, the number of his cottage in case he was on vacation, a pager number, and so on, on the theory that you could just keep dialing numbers in the case of a crisis and find him. So how do you implement that in the database? Well, you *could* simply add a new column for each type of phone number: one for home number, one for office number, one for fax number, one of pager, one for the cottage number, etc. etc. Or, you could use Shawn's idea and store the phone numbers in a separate table. If you stored the phone numbers the way Shawn described, you wouldn't have to change the Member table at all, even if several new types of phone numbers emerged over the years; in that case, you'd just add the code for the new phone number type, say 'STCB' for Star Trek Comm Badge, and then store all STCB numbers in the Phone Number table, right along with the home phone numbers, cell phone numbers, etc. Whether to break out the phone numbers that way or just keep adding new phone number columns to the Member table is up to you; both are valid and both have their positive and negative consequences. Speaking strictly for myself, I tend to break things like that out into their own tables as soon as I anticipate that the situation might happen. But others might prefer to keep things like phone numbers in the Member table until they see a real increase in phone number types. That's a judgement call based on your experience. You don't have a lot of experience yet so you might settle for flipping a coin ;-) But seriously, you should try to reason through the consequences, positive and negative, of each approach and then choose the one that you think best in light of what you expect will happen in your environment. If you rarely phone members, maybe you don't need *any* phone numbers in your database. If you phone members but rarely need an urgent answer, just their home number might be perfectly adequate; you may wait 3 weeks (or more!) for the guy to get back from vacation because you don't have other phone numbers but you may be able to live with that. If you need to track down people very urgently within minutes, you may want to ask for every phone number they have so that you can reach them quickly in a pinch. Again, *you* have to make the final decision because you are developing a solution for your specific system. No one else knows as much about what it has to do as you do. Rhino > 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]
