On Oct 19, 2004, at 10:17 AM, [EMAIL PROTECTED] wrote:
You have already gone a long way to describing your table structure by describing your data elements and their relationships to each other. Let me try to summarize you descriptions and see if I can show you how to translate your text descriptions into table descriptions.
Ok, I'm going to just go through and make sure I'm following your translation (I've never been good at foreign languages, and SQL is apparently no different ;-)
1. There are things called "projects".
Yes... (FPP and WW) although of course all of the projects fall under one meta-project (TiM), but I guess that's taken care of by the fact that this DB will contain only information about that one meta-project.
2. Some projects have "sub-projects." (I will assume that there is at most 1 parent project per sub-project)
Yes. (I am thinking here of FPP1 and FPP2 and FPP3). So far WW has only one "sub-project" (WW1) but we expect there will be more eventually.
3. Some projects contain groups.
I would have said (to use your wording) some "sub-projects" contain "groups"... otherwise I think I'm confused what the difference is between a sub-project and a group.
I'm thinking of it this way:
fpp --> fpp1 --> fpp11
--> fpp12
--> fpp13
--> fpp14
--> fpp2 --> fpp21
--> fpp22
--> fpp23are you saying that I ought to be thinking of it this way
fpp --> fpp1
--> fpp11
--> fpp12
--> fpp13
--> fpp14
--> fpp2
--> fpp21
--> fpp22
--> fpp234. All projects contain people.
Yes.
5. Some people assigned to projects also belong to one or more groups.
Here's where it starts to get fuzzy.
You have to be in one of FPP1 or FPP2 or FPP3 or WW1 (we'll call it WW1 even though there isn't a WW2 yet).
You can be in FPP1 (or FPP2 or FPP3) and WW1.
If you are in FPP11 you can't be in FPP12 or FPP13 or FPP14 or FPP2 or FPP21 or FPP22 or FPP23. Those final groups are exclusive and required (if you are in FPP1 then you must be in one and only one of FPP11 or FPP12 or FPP13 or FPP14).
I've been thinking about it like students at a university (DB) which has several colleges (projects), and each college has graduates of a particular "class" (sub-project).
The university has no current students or alumni who aren't from a particular college and a particular year.
Some classes (sub-projects) are broken down into further groups...
We need to look at #4 before we get to #3. Because a project must have people, that implies that there is a "person" thing in your system. Create a table to store information about a "person"
CREATE TABLE person ( id int auto_increment primary key, First Name varchar(20) not null, Last Name varchar(20) not null, ... more person-related fields and indexes ... )
Now should that table have all the information about persons (name, address, email.....) ?
That was what I was originally thinking needed to be spread out into separate tables.
Now, #4 also states that each project can have 0 or more people assigned to it. What it didn't say, but should have, was whether each person can be assigned to more than one project. I know that most people do work on more than one project at a time or will be assigned to a new project after the old one is over. You have a many-to-many relationship between your people and your projects
It is possible that someone could be in only one project (ever) or that they might be involved in more than one. We want to leave the door open for the 2nd option, although it will probably be more rare that someone is in more than 1 (however, we already have some and will no doubt have others).
By declaring that the COMBINATION of the values person_id and project_id must be UNIQUE, you guarantee that nobody is assigned to the same project more than once.
Ah, so I would assume I could do the same for sub-sub-projects (FPP11, FPP12, etc)?
Looking at #3...I get the impression from the description that each group is specific to a single project and that each project can have zero or more groups (a project could just have people that aren't in any groups or no groups at all).
The projects (FPP and WW) will always have sub-projects. People have to belong to at least one project (FPP) and only one sub-project of that project (FPP1 or FPP2 or FPP3). Further division beyond that is possible *and* if possible it is mandatory that everyone is in one and only one.
You may have noticed that while we have related a "person assigned to a project" to a "project group" however there is nothing in our data definitions that will prevent you from assigning a person assigned to one project to a group assigned to a different project. That bit if business rule enforcement must come from your application. The database can do a lot but it won't do everything.
Ah, so I could theoretically mis-assign someone from WW1 to FPP11.
Does this help you get started?
YES! Many thanks for your help and your time. I wasn't even really thinking about the person-to-project aspect as much as the person-to-their-information aspect.
TjL
ps - is it common that I found it more helpful to take a pen and a piece of blank paper to outline this than try to do this on the computer? That doesn't happen to me often... of course it's much harder to share via email :-)
