Thread: Database Structure

On Wed, 2002-08-07 at 16:31, Chad Thompson wrote:
> I have 3 tables.  Clients, Projects, and Lists.  There is a one to many relation between Clients and Projects, and
Clientsand Lists. 
> That is to say that a client can have multiple projects and these projects can use any or all of his lists.
>
> This works fine, the wrinkle comes in when i have a client, who has clients who have their own lists.
> I treat this just as a client with different projects, but the lists now need to be associated with this project
only,and no longer with every project that this client has. 
>
> Should I create another table to handle these exceptions? or is there a more elegant way.

If you will only ever have two levels of indirection, you could have a
'client' column and a 'client parent' column, and perform your selects
appropriately.

However, this seems like a kludge in that you're measuring two different
types of relationships using a single table.

One relationship is between a client and a project.
The other relationship is between a client's client and a project (you
don't mention whether it's useful to know the super-parent for a given
project, but I'll assume that it is).

Something tells me to describe the relationship in different tables:

client   (id, name, ...)
projects (id, description, ...)
client_projects (client_id, projects_id)
client_cust  (client_id, customer_client_id)

So, the client_projects table relates the clients to projects.  There's
no reason why a single project might be 'owned' by several clients, and
you could query either the client's client, or by the client.

The client_cust table describes the relationship between your clients,
and their clients.

Thoughts?

-Ken