Re: 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 Clients and 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