----- Original Message ----- From: "Chris Kavanagh" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, December 06, 2004 12:11 PM Subject: A newbie and his first MySQL schema
> Dear list, > > So I'm having a bit of trouble with my first schema. I'm sure I'm > missing something idiotic here, but days of learning MySQL and setting > up servers and working in UNIX have kind of fried my brain. Okay, here > goes: > > My project management system includes (among others) two tables: > "people" and "projects". I want each project to have a list of people > that are authorised to view it. To my mind, the field ought to look a > bit like this: > > -- > Authorised list: > Chris Kavanagh > Joe Schmoe > Jane Doe > -- > > But fields can't hold multiple values, can they? And on my schema, it > seems to be a many-to-many relationship between the two tables, and I > heard that they are the work of the Devil and must be shunned. I'm > sure I need to make a new table or something, but I'm not really sure > which one. Can anyone help me? > First of all, there is no problem with storing a value like "Chris Kavanagh" in a single column; a column defined as char() or varchar() or even the BLOB should store that data just fine. You just have to make sure that the column is defined large enough to hold the largest value that you expect to store. Of course, it is often a good idea to store the different parts of a name in separate columns so that you can search on them individually. This can also help with your understanding of the data. For instance, some Chinese people I meet give me their last name first and then their first name, e.g. Lee Xian, in the Chinese fashion (Remember that in the name "Mao Tse Tung", "Mao" was his family name, not his first name). Other Chinese people give me their first name first and then their last name, e.g. Xian Lee, the way we usually do in the West. However, if you simply stored "Xian Lee" (or "Lee Xian") in a single column and then had a requirement to return all of the rows were the first name was "Lee", you might get confused when it comes to "Lee Xian": is "Lee" his/her first name or family name? This situation could easily happen if the input form that provided the data in the first place simply called for the entire customer name to be entered in a single field. On the other hand, if the form (and the underlying table) separated first name and last name into two separate fields, you also know if "Lee" was the person's first name or last name. As for your other question, yes, many-to-many relationships are virtually always split into a pair of one-to-many relationships for many good reasons. In your case, you will have a table for people, a table for projects, and a new table, usually called an "intersection" (or "association") table, to show the relationships between people and projects. You're going to end up with something like this: People - one row for each employee, primary key employee ID ==== EmpID Lastname Firstname ... 1 Kavanagh Chris 2 Schmoe Joe 3 Doe Jane Project - one row for each project, primary key project ID ===== ProjID ProjName .... A Marketing System B Shipping System C Purchasing System People_Project - one row for each person/project combination that actually exists ========== EmpID ProjID 1 B 1 C 2 A 3 A 3 C In other words, employee 1 works on projects B and C but not A. Employee 2 works only on project A. Employee 3 works on projects A and C but not B. The primary key for the intersection table is the COMBINATION of EmpID and ProjID! Neither column by itself would make sense as the primary key of the table; the EmpID and ProjID need to be combined to form the primary key. It is now possible to store as many projects for an employee as you like but no way to say that Employee 1 is on Project B *twice*. That's exactly what you want. As a "bonus" feature, the intersection table can have additional columns if that is appropriate. For example, each employee was dedicated to each project for a dedicated percentage of their time, you could put that in the intersection table since it is information about the person/project combination. Then, you might end up with something like this: People_Project ========== EmpID ProjID Percent 1 B 50 1 C 50 2 A 100 3 A 25 3 C 75 In other words, employee 1 splits there time 50/50 between projects B and C; employee 2 spends all of their time on project A; employee 3 spends 25% of their time on project A and 75% on project C. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
