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.
1. There are things called "projects".
2. Some projects have "sub-projects." (I will assume that there is at most
1 parent project per sub-project)
3. Some projects contain groups.
4. All projects contain people.
5. Some people assigned to projects also belong to one or more groups.
Let's do the easy one now:
CREATE TABLE project (
id int auto_increment primary key,
Name varchar(20) not null,
...other project related fields and indexes...
)
There are three ways to define tables for sub-projects. One is
self-referential. This supports only one parent per sub-project but can
extend into many layers of sub-sub projects.
CREATE TABLE project (
id int auto_increment primary key,
parentproject_id int not null default(0),
Name varchar(20) not null,
...other project related fields and indexes...
)
One is explicitly parent-child. The disadvantage to this method is if you
need to reference a project/sub-project you need to check or decide
between two different values that reside on two different table.
CREATE TABLE project (
id int auto_increment primary key,
Name varchar(20) not null,
...other project related fields and indexes...
)
CREATE TABLE subproject (
id int auto_increment primary key,
Name varchar(20) not null,
...other project related fields and indexes...
)
The third is semi-self-referential in that all of the projects and
subprojects are listed in the same table but their relationship (parent to
child) is maintained in a third table. This method supports sub-projects
that can be children of multiple projects. The danger here is you can
possibly create a circular reference (A is a parent of B. B is a parent of
C. C is a parent of A....)
CREATE TABLE project (
id int auto_increment primary key,
Name varchar(20) not null,
...other project related fields and indexes...
)
CREATE TABLE subprojects (
project_id int not null,
subproject_id int not null,
... declare keys and indexes here...
)
In the table subprojects, both columns get the ID values from two
different project records.
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, #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
create table people_projects (
id int auto_increment primary key,
person_id int not null,
project_id int not null,
UNIQUE(person_id, project_id)
...other indexes as needed...
)
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.
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).
CREATE TABLE projectgroup (
id int auto_increment primary key,
project_id int not null,
name varchar(20) not null,
...other fields and keys as necessary...
)
Number 5 is an interesting relationship. It is declaring a relationonship
on a relationship. The "people assigned to a project" objects are on the
"people_projects" table, not the person table. We need to equate those
people to one or more groups. Again we are in a many-to-many situation and
model it this way:
CREATE TABLE peopleproject_projectgroups (
id int auto_increment primary key,
peopleproject_id int not null,
projectgroup_id int not null,
UNIQUE (peopleproject_id, group_id)
... other indexes...
)
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.
Does this help you get started?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Timothy Luoma <[EMAIL PROTECTED]> wrote on 10/18/2004 05:49:22 PM:
>
> Ok, this makes a lot of sense now. (As usual, what seems like more
> work initially pays off in the end.)
>
> Here's a specific question.
>
> The parent project is called "TiM". We will, at times, want to pull
> out information for *everyone*. But more often we will want to pull
> out information just from sub-projects. One of these is "FPP" and
> another is "WW".
>
> WW right now has only taken place once and did not have any further
> sub-divisions.
>
> FPP will span about 5 years, and be divided into 3 groups (and possibly
> more after the initial pilot project).
>
> The people who are in the first group of FPP participants are referred
> to as "FPP1", the second are "FPP2" (FPP3 has not started yet).
>
> FPP1 involves 28 people broken down into 4 groups (FPP11, FPP12, FPP13,
> FPP14).
>
> FPP2 involves 19 people broken down into 3 groups (FPP21, FPP22, FPP23).
>
> I want to be able to match all those who are in the DB (i.e. "any TiM
> participant"), or just those who are in FPP, or just those who are in
> FPP1, or just those who are in FPP11 (or FPP23, etc).
>
> I'm having trouble figuring out how to be that specific and that broad
> at the same time. Just not quite getting my head wrapped around it.
>
> TjL
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
